SQL column Career

Normal row and column conversions
(Aixinjueluo Yu Hua 2007-11-18 in Sanya, Hainan)

Let's say a student score table (TB) is as follows:
Name Subject Result
Zhang San language 74
Zhang San Mathematics 83
Zhang San Physics 93
John Doe Language 74
John Doe Mathematics 84
John Doe Physics 94
*/

/*
Want to become
Name Chinese mathematics Physics

Lee 474 84 94
Sheet 374 83 93
*/

CREATE TABLE TB
(
Name varchar (10),
Subject varchar (10),
Result int
)

INSERT into TB (Name, Subject, Result) VALUES (' Zhang San ', ' language ', 74)
INSERT into TB (Name, Subject, Result) VALUES (' Zhang San ', ' math ', 83)
INSERT into TB (Name, Subject, Result) VALUES (' Zhang San ', ' physical ', 93)
INSERT into TB (Name, Subject, Result) VALUES (' John Doe ', ' language ', 74)
INSERT into TB (Name, Subject, Result) VALUES (' John Doe ', ' math ', 84)
INSERT into TB (Name, Subject, Result) VALUES (' John Doe ', ' physical ', 94)
Go

– Static SQL, refers to subject only language, mathematics, physics, this course.
Select name Name,
Max (case subject when ' language ' then result else 0 end) language,
Max (case subject when ' math ' then result else 0 end) Math,
Max (case subject when ' physical ' then result else 0 end) physical
From TB
Group BY name
/*
Name Chinese mathematics Physics

Lee 474 84 94
Sheet 374 83 93
*/

– Dynamic SQL, refers to subject more than language, mathematics, physics, this course.
DECLARE @sql varchar (8000)
Set @sql = ' select name ' + ' name '
Select @sql = @sql + ', max (case Subject if "' + Subject + ' then Result else 0 end) [' + Subject + '] '
From (select distinct Subject from TB) as a
Set @sql = @sql + ' from TB GROUP by name '
EXEC (@sql)
/*
Name Mathematical Physics language

Lee 484 94 74
Sheet 383 93 74
*/

/* Add an average score, total score
The average total score of the mathematical physics of the name Chinese

Lee 474 84 94 84.00 252
Sheet 374 83 93 83.33 250
*/

– Static SQL, refers to subject only language, mathematics, physics, this course.
Select name Name,
Max (case subject when ' language ' then result else 0 end) language,
Max (case subject when ' math ' then result else 0 end) Math,
Max (case subject when ' physical ' then result else 0 end) Physics,
The average of cast (avg (result*1.0) as Decimal (18,2)),
SUM (Result) Total score
From TB
Group BY name
/*
The average total score of the mathematical physics of the name Chinese

Lee 474 84 94 84.00 252
Sheet 374 83 93 83.33 250
*/

– Dynamic SQL, refers to subject more than language, mathematics, physics, this course.
DECLARE @sql1 varchar (8000)
Set @sql1 = ' select name ' + ' name '
Select @sql1 = @sql1 + ', max (case Subject if "' + Subject + ' then Result else 0 end) [' + Subject + '] '
From (select distinct Subject from TB) as a
Set @sql1 = @sql1 + ', CAST (avg (result*1.0) as Decimal (18,2)) average, sum (result) score from TB Group by name '
EXEC (@sql1)
/*
Name Mathematics physical language average total score

Lee 484 94 74 84.00 252
Sheet 383 93 74 83.33 250
*/

DROP table TB

———————————————————

/*
If the above two tables are exchanged:

Name Chinese mathematics Physics
Sheet 374 83 93
Lee 474 84 94

Want to become
Name Subject Result

John Doe Language 74
John Doe Mathematics 84
John Doe Physics 94
Zhang San language 74
Zhang San Mathematics 83
Zhang San Physics 93
*/

CREATE TABLE Tb1
(
Name varchar (10),
language int,
math int,
Physical int
)

Insert into TB1 (name, language, math, physics) VALUES (' Zhang San ', 74,83,93)
Insert into TB1 (name, language, math, physics) VALUES (' John Doe ', 74,84,94)

SELECT * FROM
(
Select name as name, Subject = ' language ', Result = language from tb1
UNION ALL
Select name as name, Subject = ' math ', Result = Math from tb1
UNION ALL
Select name as name, Subject = ' physical ', Result = physical from tb1
) T
Order by name, Case Subject when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 when ' total ' then 4 end

/* Add an average score, total score
Name Subject Result
———- ——- ——————–
John Doe Language 74.00
John Doe Mathematics 84.00
John Doe Physics 94.00
Li Shiping Evenly 84.00
John Doe out of 252.00
Zhang San language 74.00
Zhang San Mathematics 83.00
Zhang San Physics 93.00
Zhang San average score 83.33
Zhang San out of 250.00
*/

SELECT * FROM
(
Select name as name, Subject = ' language ', Result = language from tb1
UNION ALL
Select name as name, Subject = ' math ', Result = Math from tb1
UNION ALL
Select name as name, Subject = ' physical ', Result = physical from tb1
UNION ALL
Select name as name, Subject = ' average score ', Result = cast ((language + math + physics) *1.0/3 as Decimal (18,2)) from TB1
UNION ALL
Select name as name, Subject = ' total Score ', Result = language + math + physics from TB1
) T
Order by name, Case Subject when ' language ' then 1 when ' math ' then 2 when ' physical ' then 3 when ' average ' then 4 when ' total ' then 5 end

drop table Tb1

Reference article:
Http://www.cnblogs.com/cpcpc/archive/2013/04/08/3009021.html
http://blog.csdn.net/vipxiaotian/article/details/4409423

SQL column Career