Database row and column Conversion

-- 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 ',78Union all
 

 
Select n 'zhang san', N 'mat ',87Union all
 

 
Select n 'zhang san', N 'English ',82Union all
 

 
Select n 'zhang san', N 'physical ',90Union all
 

 
Select n 'Li si', N 'China ',65Union all
 

 
Select n 'Li si', N 'mat ',77Union all
 

 
Select n 'Li si', N 'English ',65Union 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] else0End ),
 

 
[Physical] = max (case when [course] = 'physical 'Then [score] else0End ),
 

 
[English] = max (case when [course] = 'English 'Then [score] else0End ),
 

 
[Language] = max (case when [course] = 'China' then [score] else0End)
 

 
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] else0End ),
 

 
[Physical] = max (case when [course] = 'physical 'Then [score] else0End ),
 

 
[English] = max (case when [course] = 'English 'Then [score] else0End ),
 

[Language] = max (case when [course] = 'China' then [score] else0End ),
 

 
[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), [Mathematics] int, [physical] int, [English] int, [Chinese] INT)
 

 
Insert class
 

 
Select n 'Li si ',77,85,65,65Union 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