SQL result set conversion-with row-to-column 2

/*
Question: Suppose there is a student Score Table (score) as follows:
Sname subject scores
Wang Gang's Chinese 75
Wang Gang mathematics 83
Wang Gang English 93
Li Jun Chinese 74
Li Jun mathematics 84
Li Jun English 94
(The following result is displayed ):
Sname Chinese math English
----------------
Li Jun 74 84 94
Wang Gang 75 83 93
-------------------
*/
Create Table Tb (sname varchar (10), subject varchar (10), scores INT)
Insert into TB values ('wang gang', 'China', 75)
Insert into TB values ('wang gang', 'mat', 83)
Insert into TB values ('wang gang', 'English ', 93)
Insert into TB values ('jun li ', 'China', 74)
Insert into TB values ('Li Jun ', 'mat', 84)
Insert into TB values ('Li Jun ', 'English', 94)
Go
-- SQL Server 2000 static SQL refers to subject, which only includes Chinese, mathematics, and English. (Same as below)
Select sname as sname,
Max (case subject when 'China' then scores else 0 end) language,
Max (case subject when 'mate' then scores else 0 end) math,
Max (case subject when 'English 'Then scores else 0 end) English
From TB
Group by sname
-- SQL Server 2000 dynamic SQL refers to subject, which is not only a Chinese, a mathematical, or an English subject. (Same as below)
Declare @ SQL varchar (8000)
Set @ SQL = 'select sname'
Select @ SQL = @ SQL + ', max (case subject when ''' + Subject + ''' then scores else 0 end) [' + Subject + ']'
From (select distinct subject from TB) as
Set @ SQL = @ SQL + 'from TB group by sname'
Exec (@ SQL)
-- SQL Server 2005 static SQL.
Select * from TB a branch (max (scores) for subject in (Chinese, mathematics, English) B
-- SQL Server 2005 dynamic SQL.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + '], [', '') + subject from TB group by subject
Set @ SQL = '[' + @ SQL + ']'
Exec ('select * from (select * from TB) a round (max (scores) for subject in ('+ @ SQL +') B ')
---------------------------------
/*
Problem: Based on the above results, the average score and total score are added. The following result is obtained:
Average score of sname Chinese Mathematical English
--------------------------
Li Jun 74 84 94 84.00 252
Wang Gang 75 83 93 83.33 251
*/
-- SQL Server 2000 static SQL.
Select sname,
Max (case subject when 'China' then scores else 0 end) language,
Max (case subject when 'mate' then scores else 0 end) math,
Max (case subject when 'then scores else 0 end) English,
Cast (AVG (scores * 1.0) as decimal () average score,
Sum (scores) total score
From TB
Group by sname
-- SQL Server 2000 dynamic SQL.
Declare @ SQL varchar (8000)
Set @ SQL = 'select sname'
Select @ SQL = @ SQL + ', max (case subject when ''' + Subject + ''' then scores else 0 end) [' + Subject + ']'
From (select distinct subject from TB) as
Set @ SQL = @ SQL + ', average score of cast (AVG (scores * 1.0) as decimal (), total score of sum (scores) from TB group by sname'
Exec (@ SQL)
-- SQL Server 2005 static SQL.
Select M. *, N. Average score, N. Total score from
(Select * from TB) a branch (max (scores) for subject in (Chinese, mathematics, English) B) m,
(Select sname, cast (AVG (scores * 1.0) as decimal () average score, sum (scores) total score from TB group by sname) N
Where M. sname = n. sname
-- SQL Server 2005 dynamic SQL.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + ',', '') + subject from TB group by subject
Exec ('select M. *, N. Average score, N. Total score from
(Select * from TB) a branch (max (scores) for subject in ('+ @ SQL +') B) m,
(Select sname, cast (AVG (scores * 1.0) as decimal () average score, sum (scores) total score from TB group by sname) N
Where M. sname = n. sname ')
Drop table TB
------------------
------------------
/*
Question: If the two tables change each other: the table structure and data are:
Sname Chinese math English
Wang Gang 75 83 93
Li Jun 74 84 94
(The following result is displayed ):
Sname subject scores
------------
Li Jun Chinese 74
Li Jun mathematics 84
Li Jun English 94
Wang Gang's Chinese 75
Wang Gang mathematics 83
Wang Gang English 93
--------------
*/
Create Table Tb (sname varchar (10), Chinese int, math int, English INT)
Insert into TB values ('wang gang', 93)
Insert into TB values ('Li Jun ', 94)
Go
-- SQL Server 2000 static SQL.
Select * from
(
Select sname, subject = 'China', scores = Chinese from TB
Union all
Select sname, subject = 'mat', scores = mathematics from TB
Union all
Select sname, subject = 'English ', scores = English from TB
) T
Order by sname, case subject when 'chine' then 1 when' math 'then 2 when' then 3 end
-- SQL Server 2000 dynamic SQL.
-- Call the dynamic ecosystem of the system table.
Declare @ SQL varchar (8000)
Select @ SQL = isnull (@ SQL + 'Union all', '') + 'select sname, [subject] = '+ quotename (name, ''') + ', [scores] = '+ quotename (name) + 'from tb'
From syscolumns
Where name! = N'sname' and ID = object_id ('tb') -- table name TB, excluding other columns whose names are sname
Order by colid ASC
Exec (@ SQL + 'order by sname ')
-- SQL Server 2005 static SQL.
Select sname, subject, scores from TB unregister (scores for subject in ([language], [mathematics], [English]) T
-- SQL Server 2005 dynamic SQL, same as SQL Server 2000 dynamic SQL.
--------------------
/*
Problem: add an average score and the total score to the above result. The following result is obtained:
Sname subject scores
----------------
Li Jun Chinese 74.00
Li Jun, mathematics 84.00
Li Jun English 94.00
Li Jun has an average score of 84.00
Li Jun's total score is 252.00
Wang Gang Chinese 75.00
Wang Gang, mathematics 83.00
Wang Gang English 93.00
Wang Gang has an average score of 83.33.
Wang Gang's total score is 251.00
------------------
*/
Select * from
(
Select sname as sname, subject = 'China', scores = Chinese from TB
Union all
Select sname as sname, subject = 'mat', scores = math from TB
Union all
Select sname as sname, subject = 'English ', scores = English from TB
Union all
Select sname as sname, subject = 'average', scores = cast (Chinese + mathematics + English) * 1.0/3 as decimal () from TB
Union all
Select sname as sname, subject = 'Total', scores = Chinese + mathematics + English from TB
) T
Order by sname, 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 TB

This article from the csdn blog, reproduced please indicate the source: http://blog.csdn.net/coleling/archive/2010/10/29/5973766.aspx