[sql server] 行轉列問題總結 – 2、列轉行

if not object_id('Class') is null
    drop table Class
Go
Create table Class([Student] nvarchar(2),[數學] int,[物理] int,[英語] int,[語文] int)
Insert Class
select N'李四',77,85,65,65 union all
select N'張三',87,90,82,78
Go

--2000:

動態:

declare @s nvarchar(4000)
select @s=isnull(@s+' union all ','')+'select [Student],[Course]='+quotename(Name,'''')--isnull(@s+' union all ','') 去掉字串@s中第一個union all
+',[Score]='+quotename(Name)+' from Class'
from syscolumns where ID=object_id('Class') and Name not in('Student')--排除不轉換的列
order by Colid
exec('select * from ('+@s+')t order by [Student],[Course]')--增加一個排序

產生靜態:
select *
from (select [Student],[Course]='數學',[Score]=[數學] from Class union all
select [Student],[Course]='物理',[Score]=[物理] from Class union all
select [Student],[Course]='英語',[Score]=[英語] from Class union all
select [Student],[Course]='語文',[Score]=[語文] from Class)t
order by [Student],[Course]

go
--2005:

動態:

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 unpivot ([Score] for [Course] in('+@s+'))b')

go
select
    Student,[Course],[Score]
from
    Class
unpivot
    ([Score] for [Course] in([數學],[物理],[英語],[語文]))b

產生格式:
/*
Student Course Score
------- ------- -----------
李四      數學      77
李四      物理      85
李四      英語      65
李四      語文      65
張三      數學      87
張三      物理      90
張三      英語      82
張三      語文      78

(8 行受影響)
*/