行列轉換sql_server指令碼

SQL code--行列互轉
/******************************************************************************************************************************************************
以學產生績為例子，比較形象易懂

整理人：中國風(Roy)

日期:2008.06.06
******************************************************************************************************************************************************/

--1、行互列
--> --> (Roy)產生測試數據
 
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'張三',N'語文',78 union all
select N'張三',N'數學',87 union all
select N'張三',N'英語',82 union all
select N'張三',N'物理',90 union all
select N'李四',N'語文',65 union all
select N'李四',N'數學',77 union all
select N'李四',N'英語',65 union all
select N'李四',N'物理',85
Go
--2000方法:
動態:

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]')

產生靜態:

select
    [Student],
    [數學]=max(case when [Course]='數學' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英語]=max(case when [Course]='英語' then [Score] else 0 end),
    [語文]=max(case when [Course]='語文' then [Score] else 0 end)
from
    Class
group by [Student]

GO
動態:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course]
exec('select * from Class pivot (max([Score]) for [Course] in('+@s+'))b')

產生靜態:
select *
from
    Class
pivot
    (max([Score]) for [Course] in([數學],[物理],[英語],[語文]))b

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

(2 行受影響)
*/

------------------------------------------------------------------------------------------
go
--加上總成績(學科平均分)

--2000方法:
動態:

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+',[總成績]=sum([Score])  from Class group by [Student]')--加多一列(學科平均分用avg([Score]))

產生動態:

select
    [Student],
    [數學]=max(case when [Course]='數學' then [Score] else 0 end),
    [物理]=max(case when [Course]='物理' then [Score] else 0 end),
    [英語]=max(case when [Course]='英語' then [Score] else 0 end),
    [語文]=max(case when [Course]='語文' then [Score] else 0 end),
    [總成績]=sum([Score]) --加多一列(學科平均分用avg([Score]))
from
    Class
group by [Student]

go

--2005方法:

動態:

declare @s nvarchar(4000)
Select     @s=isnull(@s+',','')+quotename([Course]) from Class group by[Course] --isnull(@s+',','') 去掉字串@s中第一個逗號
exec('select [Student],'+@s+',[總成績] from (select *,[總成績]=sum([Score])over(partition by [Student]) from Class) a
pivot (max([Score]) for [Course] in('+@s+'))b ')

產生靜態:

select
    [Student],[數學],[物理],[英語],[語文],[總成績]
from
    (select *,[總成績]=sum([Score])over(partition by [Student]) from Class) a --平均分時用avg([Score])
pivot
    (max([Score]) for [Course] in([數學],[物理],[英語],[語文]))b

產生格式:

/*
Student 數學          物理          英語          語文          總成績
------- ----------- ----------- ----------- ----------- -----------
李四      77          85          65          65          292
張三      87          90          82          78          337

(2 行受影響)
*/

go

--2、列轉行
--> --> (Roy)產生測試數據
 
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 行受影響)
*/