浅谈SQLServer行列转换PIVOT函数的使用

Create Table Students(Name varchar(10), Subject Nvarchar(10),Score int)
Insert into Students
Select 'Andy' ,'Chiness',round(60+40*rand(),0) Union all
Select 'Burgess' ,'Chiness',round(60+40*rand(),0) Union all
Select 'Ula' ,'Chiness',round(60+40*rand(),0) Union all
Select 'Lily' ,'Chiness',round(60+40*rand(),0) Union all
Select 'Demon' ,'Chiness',round(60+40*rand(),0) Union all
Select 'Andy' ,'Math',round(60+40*rand(),0) Union all
Select 'Burgess' ,'Math',round(60+40*rand(),0) Union all
Select 'Ula' ,'Math',round(60+40*rand(),0) Union all
Select 'Lily' ,'Math',round(60+40*rand(),0) Union all
Select 'Demon' ,'Math',round(60+40*rand(),0) Union all
Select 'Andy' ,'English',round(60+40*rand(),0) Union all
Select 'Burgess' ,'English',round(60+40*rand(),0) Union all
Select 'Ula' ,'English',round(60+40*rand(),0) Union all
Select 'Lily' ,'English',round(60+40*rand(),0) Union all
Select 'Demon' ,'English',round(60+40*rand(),0)

SELECT * FROM Students
PIVOT(MAX(SCORE) FOR Subject IN(Chiness,Math,English) ) AS PVT

SELECT * FROM (
--这里写复杂的sql查询语句
) a
PIVOT(--此次省略相关code) AS PVT

SELECT * FROM (
SELECT * FROM Students
) a
PIVOT(MAX(SCORE) FOR Subject IN(Chiness,Math,English) ) AS PVT