SQL column to line, SQL column to line

SQL column to line, SQL column to line

Normal row/column Conversion
(AI xinjue Luo. Yu Hua in Sanya, Hainan)

Assume that there is a student renewal table (tb) as follows:
Name Subject Result
Zhang San Language 74
James math 83
Zhang San physical 93
Li Si language 74
Li Si mathematics 84
Li Si physical 94
*/

/*
Want to become
Name, Chinese, Mathematics, Physics

Li Si 74 84 94
Zhang San 74 83 93
*/

Create table tb
(
Name varchar (10 ),
Subject varchar (10 ),
Result int
)

Insert into tb (Name, Subject, Result) values ('zhang san', 'China', 74)
Insert into tb (Name, Subject, Result) values ('zhang san', 'mat', 83)
Insert into tb (Name, Subject, Result) values ('zhang san', 'Physical ', 93)
Insert into tb (Name, Subject, Result) values ('Li si', 'China', 74)
Insert into tb (Name, Subject, Result) values ('lily', 'mat', 84)
Insert into tb (Name, Subject, Result) values ('lily', 'Physical ', 94)
Go

-Static SQL indicates that subject has only three courses: Chinese, mathematics, and physics.
Select name,
Max (case subject when 'China' then result else 0 end) language,
Max (case subject when 'mate' 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

Li Si 74 84 94
Zhang San 74 83 93
*/

-Dynamic SQL refers to subject, which includes more than three courses: Chinese, mathematics, and physics.
Declare @ SQL varchar (8000)
Set @ SQL = 'select Name as '+ 'name'
Select @ SQL = @ SQL + ', max (case Subject when "' + Subject +" 'then Result else 0 end) ['+ Subject +']'
From (select distinct Subject from tb) as
Set @ SQL = @ SQL + 'from tb group by name'
Exec (@ SQL)
/*
Name, mathematics, physics, and Chinese

Li Si 84 94 74
Zhang San 83 93 74
*/

/* Add an average score, total score
Name, Chinese, mathematics, and physics average score

Li Si 74 84 94 84.00 252
Zhang San 74 83 93 83.33 250
*/

-Static SQL indicates that subject has only three courses: Chinese, mathematics, and physics.
Select name,
Max (case subject when 'China' then result else 0 end) language,
Max (case subject when 'mate' then result else 0 end) math,
Max (case subject when 'physical 'then result else 0 end) physical,
Cast (avg (result * 1.0) as decimal () average score,
Sum (result) total score
From tb
Group by name
/*
Name, Chinese, mathematics, and physics average score

Li Si 74 84 94 84.00 252
Zhang San 74 83 93 83.33 250
*/

-Dynamic SQL refers to subject, which includes more than three courses: Chinese, mathematics, and physics.
Declare @ sql1 varchar (8000)
Set @ sql1 = 'select Name as '+ 'name'
Select @ sql1 = @ sql1 + ', max (case Subject when "' + Subject +" 'then Result else 0 end) ['+ Subject +']'
From (select distinct Subject from tb) as
Set @ sql1 = @ sql1 + ', cast (avg (result * 1.0) as decimal () average score, sum (result) total score from tb group by name'
Exec (@ sql1)
/*
Name, mathematics, physics, and Chinese average score

Li Si 84 94 74 84.00 252
Zhang San 83 93 74 83.33 250
*/

Drop table tb

-------------------

/*
If the two tables change each other:

Name, Chinese, Mathematics, Physics
Zhang San 74 83 93
Li Si 74 84 94

Want to become
Name Subject Result

Li Si language 74
Li Si mathematics 84
Li Si physical 94
Zhang San Language 74
James math 83
Zhang San physical 93
*/

Create table tb1
(
Name varchar (10 ),
Chinese int,
Mathematical int,
Physical int
)

Insert into tb1 (name, language, mathematics, physics) values ('zhang san', 93)
Insert into tb1 (name, language, mathematics, physics) values ('Li si', 94)

Select * from
(
Select Name as Name, Subject = 'China', Result = language from tb1
Union all
Select Name as Name, Subject = 'mat', Result = mathematics from tb1
Union all
Select Name as Name, Subject = 'physical ', Result = physical from tb1
) T
Order by name, case Subject when 'chine' then 1 when' math 'then 2 when' then 3 when' total score 'then 4 end

/* Add an average score, total score
Name Subject Result
--------------
Li Si language 74.00
Li Si, mathematics 84.00
Li Si physical 94.00
Li Si average score 84.00
Li Si's total score is 252.00
Zhang San Chinese 74.00
Zhang San, mathematics 83.00
Zhang San physical 93.00
Michael Jacob has an average score of 83.33.
Zhang San's total score is 250.00
*/

Select * from
(
Select Name as Name, Subject = 'China', Result = language from tb1
Union all
Select Name as Name, Subject = 'mat', Result = mathematics from tb1
Union all
Select Name as Name, Subject = 'physical ', Result = physical from tb1
Union all
Select Name as Name, Subject = 'average', Result = cast (Language + mathematics + physics) * 1.0/3 as decimal () from tb1
Union all
Select Name as Name, Subject = 'Total', Result = language + mathematics + physics from tb1
) T
Order by name, case Subject when 'chine' then 1 when' math 'then 2 when' then 3 when' average score 'then 4 when' total score 'then 5 end

Drop table tb1

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