Reprint: http://www.cnblogs.com/newwind521/archive/2010/11/25/1887203.html
SQL row Line column conversion for normal row/*
Title: Normal row and column conversions (version 2.0)
Aixinjueluo. Yuk Wah
Date: 2008-03-09
Location: Shenzhen, Guangdong
Description: Normal row and column conversions (version 1.0) provide static and dynamic notation only for SQL Server 2000, and version 2.0 increases the syntax for SQL Server 2005.
Question: Suppose there is a student score table (TB) as follows:
Name Course Score
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 (get the following result):
Name Chinese mathematics Physics
---- ---- ---- ----
Lee 474 84 94
Sheet 374 83 93
-------------------
*/
CreateTableTb(Namevarchar(10),Coursevarchar(10),Fractional int)
Insert intoTbValues(' Zhang San ',' Chinese ',74)
Insert intoTbValues(' Zhang San ',' Math ',83)
Insert intoTbValues(' Zhang San ',' Physical ',93)
Insert intoTbValues(' John Doe ',' Chinese ',74)
Insert intoTbValues(' John Doe ',' Math ',84)
Insert intoTbValues(' John Doe ',' Physical ' ,94)
Go
--sql SERVER 2000 static SQL, refers to the course only language, mathematics, physics, this course. (hereinafter)
Select name as name ,
Max ( case course when ' language ' then Score else 0 end ) language ,
Max ( case course when ' math ' then score else 0 End ) Math ,
Max ( Case course when ' physical ' then score else 0 End ) physical ,
from tb ,
group by name
--sql SERVER 2000 Dynamic SQL, refers to the course of more than language, mathematics, physics, this course. (hereinafter)
Declare@Sqlvarchar(8000)
Set@Sql=' select name '
Select@Sql=@Sql+', max (case course when '"'+Course+ "'' then fractions else 0 end) ['+Course+'] '
from(SelectdistinctCourse fromTb) asA
Set@Sql=@Sql+' from TB Group by name '
Exec(@Sql)
--sql SERVER 2005 static SQL.
Select * from (Select * from TB) a pivot (max( Score ) for course in ( language , mathematics , physics )) b
--sql SERVER 2005 Dynamic SQL. &NBSP
declare @ sql varchar ( 8000 )
select @ sql = isnull ( @ sql + '],[' , ' ) + course from tb group by Course
set @ sql = ' [' + @ sql + '] '
exec ( ' SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' + @ SQL&NB Sp + ')] B ' )
---------------------------------
/*
Question: On the basis of the above results, the average score, the total score, the following results are obtained:
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
*/
--sql SERVER 2000 static SQL.
SelectName and name,
Max(Case Course When' Chinese ' ThenScoresElse0End)Chinese,
Max(Case Course When' Math ' ThenScoresElse0End)Mathematical,
Max(Case Course When' Physical ' ThenScoresElse0End)Physical,
cast(avg(Scores*1.0) asdecimal(18,2))Average score,
sum(Scores)Score
fromTb
Group byName
--sql SERVER 2000 Dynamic SQL.
Declare@Sqlvarchar(8000)
Set@Sql=' select name '
Select@Sql=@Sql+', max (case course when '"'+Course+"'' then fractions else 0 end) [' +Course+'] '
from(SelectdistinctCourse fromTb) asA
Set@Sql=@Sql+', CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB group by name '
Exec(@Sql)
--sql SERVER 2005 static SQL.
SelectM.*,N.Average score,N.Score from
(Select* from(Select* fromTb)a pivot(Max(Scores) forCourseinch(Chinese,Mathematical,Physical))B)M,
(SelectName,cast(avg(Scores*1.0) asdecimal(18,2))Average score,sum(Scores)Score fromTbGroup byName)N
whereM.Name=N.Name
--sql SERVER 2005 Dynamic SQL.
DECLARE @SQL varchar(8000)
Select @SQL = isnull(@SQL + ', ' , ') ) + courses from the TB group by course
EXEC ( ' Select m.*, N. mean score, N. Total score from
(SELECT * FROM (SELECT * from TB) a pivot (max (score) for course in (' + @SQL + ')) b) m,
(select Name, CAST (avg (fractional *1.0) as decimal (18,2)) average, sum (score) total from TB Group by name) n
where M. Name = N. Name ')
Drop Table TB
------------------
------------------
/*
Question: If the above two tables are exchanged: The table structure and data are:
Name Chinese mathematics Physics
Sheet 374 83 93
Lee 474 84 94
Want to become (get the following result):
Name Course Score
---- ---- ----
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 TableTb(Namevarchar(10),language int,Math int,Physical int)
Insert intoTbValues(' Zhang San ',74,83,93)
Insert intoTbValues(' John Doe ',74,84,94)
Go
--sql SERVER 2000 static SQL.
Select* from
(
SelectName,Course=' Chinese ',Scores=Chinese fromTb
Union All
SelectName,Course=' Math ',Scores=Mathematical fromTb
Union All
SelectName,Course=' Physical ',Scores=Physical fromTb
)T
Order byName,Case Course When' Chinese ' Then1 when' Math ' Then2 when' Physical ' Then3End
--sql SERVER 2000 Dynamic SQL.
--invoke system table dynamic ecology. &NBSP
declare @ sql varchar ( 8000 )
select @ sql = isnull ( @ sql + ' UNION ALL ' , ' ) + ' select name, [course] = ' + quotename ( name , " " ) + ', [score] = ' + quotename ( Name ) + ' from TB '
from syscolumns
where = n ' name ' and id = object_id ( ' TB ' ) --table name TB, which does not contain other columns named as names
Order by colid ASC
EXEC(@SQL + ' ORDER by name ')
--sql SERVER 2005 Dynamic SQL.
Select name , course , score from TB Unpivot ( score for course in ([ language ] , [ mathematics ] , [physical ])) t
--sql Server 2005 Dynamic SQL, with SQL Server 2000 dynamic SQL.
--------------------
/*
Question: In the above results, add an average score, the total score, to obtain the following results:
Name Course Score
---- ------ ------
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
(
SelectName asName,Course= ' Chinese ',Scores=Chinese fromTb
Union All
SelectName asName,Course=' Math ',Scores=Mathematical fromTb
Union All
SelectName asName,Course=' Physical ',Scores=Physical fromTb
Union All
SelectName asName,Course=' average score ',Scores=cast((Chinese+Mathematical+Physical)*1.0/3 asdecimal(18,2)) fromTb
Union All
SelectName asName,Course=' Total score ',Scores=Chinese+Mathematical+Physical fromTb
)T
Order byName,Case Course When' Chinese ' Then1 when' Math ' Then2 when' Physical ' Then3 when' average score ' Then4 when' Total score ' Then5End
DropTableTb
