Summary of SQL Server row-column Conversion

Copy codeThe Code is as follows:
-- Exchange of rows and columns
/*************************************** **************************************** **************************************** *******************************
Take student scores as an example, which is easy to understand

Organized by: Roy)

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

-- 1. Columns of rows
--> (Roy) generate a sequence number

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 'zhang san', N 'China', 78 union all
Select N 'zhang san', N 'mat', 87 union all
Select N 'zhang san', N 'English ', 82 union all
Select N 'zhang san', N 'physical ', 90 union all
Select N 'Li si', N 'China', 65 union all
Select N 'Li si', N 'mat', 77 union all
Select N 'Li si', N 'English ', 65 union all
Select N 'Li si', N 'physical ', 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],
[Mathematics] = max (case when [Course] = 'mate' then [Score] else 0 end ),
[Physical] = max (case when [Course] = 'physical 'then [Score] else 0 end ),
[English] = max (case when [Course] = 'English 'then [Score] else 0 end ),
[Language] = max (case when [Course] = 'China' 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 evaluate (max ([Score]) for [Course] in ('+ @ s +') B ')

Generate static:
Select *
From
Class
Bytes
(Max ([Score]) for [Course] in ([mathematics], [physics], [English], [Chinese]) B

Generation format:
/*
Student mathematics physics English Language
---------------------------------------------------
Li Si 77 85 65 65
Zhang San 87 90 82 78

(2 rows affected)
*/

Bytes ------------------------------------------------------------------------------------------
Go
-- Add the total score (average subject score)

-- 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] ') -- add one more column (avg ([Score]) for average subjects)

Generate dynamic:

Select
[Student],
[Mathematics] = max (case when [Course] = 'mate' then [Score] else 0 end ),
[Physical] = max (case when [Course] = 'physical 'then [Score] else 0 end ),
[English] = max (case when [Course] = 'English 'then [Score] else 0 end ),
[Language] = max (case when [Course] = 'China' then [Score] else 0 end ),
[Total Score] = sum ([Score]) -- add one more column (avg for average subject Score ([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 by [Student]) from Class)
Round (max ([Score]) for [Course] in ('+ @ s +') B ')

Generate static:

Select
[Student], [mathematics], [physics], [English], [Chinese], [total score]
From
(Select *, [total Score] = sum ([Score]) over (partition by [Student]) from Class) a -- avg ([Score]) for average Score
Bytes
(Max ([Score]) for [Course] in ([mathematics], [physics], [English], [Chinese]) B

Generation format:

/*
Student total score of mathematics, physics, English, and Chinese
--------------------------------------------------------------
Li IV 77 85 65 65 292
Zhang San 87 90 82 78 337

(2 rows affected)
*/

Go

-- 2. Column-to-row
--> (Roy) generate a sequence number

If not object_id ('class') is null
Drop table Class
Go
Create table Class ([Student] nvarchar (2), [mathematical] int, [physical] int, [English] int, [Chinese] int)
Insert Class
Select N 'Li si', union all
Select N 'zhang san', 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', '') removes the first union all in the string @ s.
+ ', [Score] =' + quotename (Name) + 'from class'
From syscolumns where ID = object_id ('class') and Name not in ('student ') -- exclude columns not converted
Order by Colid
Exec ('select * from ('+ @ s +') t order by [Student], [Course] ') -- add a sort

Generate static:
Select *
From (select [Student], [Course] = 'mat', [Score] = [mathematics] from Class union all
Select [Student], [Course] = 'physical ', [Score] = [physical] from Class union all
Select [Student], [Course] = 'English ', [Score] = [English] from Class union all
Select [Student], [Course] = 'China', [Score] = [Chinese] 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 not in ('student ')
Order by Colid
Exec ('select Student, [Course], [Score] from Class Untitled ([Score] for [Course] in ('+ @ s +') B ')

Go
Select
Student, [Course], [Score]
From
Class
Unregister
([Score] for [Course] in ([mathematics], [physics], [English], [Chinese]) B

Generation format:
/*
Student Course Score
-------------------------
Li Si math 77
Li Si physical 85
Li Si English 65
Li Si language 65
James math 87
Zhang San physical 90
James English 82
Zhang San's speech 78

(Eight rows are affected)
*/