SQL Server row and column interchange implementation summary _mssql

Copy Code code as follows:

--The ranks turn
/************************************************************************************************************** ****************************************
Take the student achievement as the example, the comparison image is easy to understand

Finishing Man: China Wind (Roy)

Date: 2008.06.06
*************************************************************************************************************** ***************************************/

--1, Row and column
-->--> (Roy) generates test data

If not object_id (' Class ') is null
drop table Class
Go
Create table Class ([Student] nvarchar (2), [Course] nvarchar (2), [Score] int)
Insert Class
Select n ' John ', n ' language ', UNION ALL
Select n ' John ', n ' math ', UNION ALL
Select n ' John ', N ' English ', the Union ALL
Select n ' John ', N ' Physics ', UNION ALL
Select n ' Dick ', n ' language ', UNION ALL
Select n ' Dick ', n ' math ', UNION ALL
Select n ' Dick ', n ' English ', UNION ALL
Select n ' Dick ', n ' Physics ', 85
Go
--2000 Method:
Dynamic:

declare @s nvarchar (4000)
Set @s= '
Select @s=@s+ ', ' +quotename ([Course]) + ' =max (case when [course]= ' +quotename ([Course], ' "') + ' then [Score] else 0 end) '
From Class group By[course]
EXEC (' SELECT [Student] ' +@s+ ' from Class Group by [Student] ')


Generate Static:

Select
[Student],
[Math]=max [course]= ' math ' then [Score] else 0 end),
[Physical]=max [course]= ' physical ' then [Score] else 0 end),
[English]=max [course]= ' English ' then [Score] else 0 end),
[Language]=max [course]= ' language ' then [Score] else 0 end)
From
Class
GROUP BY [Student]

Go
Dynamic:

declare @s nvarchar (4000)
Select @s=isnull (@s+ ', ', ') +quotename ([Course]) from Class group By[course]
EXEC (' select * from Class pivot (max ([Score]) to [Course] in (' +@s+ ') b ')

Generate Static:
SELECT *
From
Class
Pivot
(Max ([Score]) for [Course] in ([Math],[Physics],[English],[language]) b

Build format:
/*
Student Mathematics Physics English language
------- ----------- ----------- ----------- -----------
Lee 477 85 65 65
Zhang 387 90 82 78

(2 rows affected)
*/

------------------------------------------------------------------------------------------
Go
--plus total score (discipline average)

--2000 Method:
Dynamic:

declare @s nvarchar (4000)
Set @s= '
Select @s=@s+ ', ' +quotename ([Course]) + ' =max (case when [course]= ' +quotename ([Course], ' "') + ' then [Score] else 0 end) '
From Class group By[course]
EXEC (' SELECT [Student] ' +@s+ ', [Total score]=sum ([Score]) from Class GROUP by [Student] ')--one more column (subject averaging AVG ([Score]))

Build Dynamic:

Select
[Student],
[Math]=max [course]= ' math ' then [Score] else 0 end),
[Physical]=max [course]= ' physical ' then [Score] else 0 end),
[English]=max [course]= ' English ' then [Score] else 0 end),
[Language]=max [course]= ' language ' then [Score] else 0 end),
[]=sum of total score ([Score])--Plus one more column (avg. ([Score])
From
Class
GROUP BY [Student]

Go

--2005 Method:

Dynamic:

declare @s nvarchar (4000)
Select @s=isnull (@s+ ', ', ') +quotename ([Course]) from Class group By[course]--isnull (@s+ ', ', ') remove the first comma from the string @s
EXEC (' SELECT [Student], ' +@s+ ', [Total score] FROM (select *,[total score]=sum ([Score]) over (partition to [Student]) from Class) a
Pivot (max ([Score]) for [Course] in (' +@s+ ') b ')

Generate Static:

Select
[student],[Mathematics],[Physics],[English],[language],[total score]
From
(select *,[Total score]=sum ([Score]) over (partition to [Student]) from Class) A--Avg avg ([Score])
Pivot
(Max ([Score]) for [Course] in ([Math],[Physics],[English],[language]) b

Build format:

/*
Student Mathematics Physics English Language total score
------- ----------- ----------- ----------- ----------- -----------
Lee 477 85 65 65 292
Zhang 387 90 82 78 337

(2 rows affected)
*/

Go

--2, Column career change
-->--> (Roy) generates test data

If not object_id (' Class ') is null
drop table Class
Go
Create table Class ([Student] nvarchar (2), [Math] int,[physics] int,[English] int,[language] int)
Insert Class
Select N ' Dick ', 77,85,65,65 UNION ALL
Select N ' John ', 87,90,82,78
Go

--2000:

Dynamic:

declare @s nvarchar (4000)
Select @s=isnull (@s+ ' union All ', ') + ' SELECT [student],[course]= ' +quotename (Name, ' "')--isnull (@s+ ' union All ', ') Remove the first union all in a string @s
+ ', [score]= ' +quotename (Name) + ' from Class '
From syscolumns where id=object_id (' Class ') and Name not in (' Student ')--excludes columns that are not converted
ORDER BY Colid
EXEC (' SELECT * FROM (' +@s+ ') t order by [Student],[course] ')--Add a sort

Generate Static:
SELECT *
From (SELECT [student],[course]= ' Mathematics ', [score]=[Mathematics] from Class UNION ALL
Select [student],[course]= ' Physics ', [score]=[physics] from Class UNION ALL
Select [student],[course]= ' English ', [score]=[English] from Class UNION ALL
Select [student],[course]= ' language ', [score]=[language] from Class t
Order BY [Student],[course]

Go
--2005:

Dynamic:

declare @s nvarchar (4000)
Select @s=isnull (@s+ ', ', ') +quotename (Name)
From syscolumns where id=object_id (' Class ') and Name isn't in (' Student ')
ORDER BY Colid
EXEC (' Select Student,[course],[score] from Class unpivot ([Score] for [Course] in (' +@s+ ')) B ')

Go
Select
Student,[course],[score]
From
Class
Unpivot
([Score] for [Course] in ([Math],[Physics],[English],[language]) b

Build format:
/*
Student Course Score
------- ------- -----------
Dick Math 77
Dick Physics 85
Dick English 65
Dick Language 65
John Math 87
John Physics 90
John English 82
John Language 78

(8 rows affected)
*/