SQL Server中行列轉換 Pivot UnPivot

PIVOT用于将列值旋轉為列名（即行轉列），在SQL Server 2000可以用聚合函數配合CASE語句實作

PIVOT的一般文法是：PIVOT(聚合函數(列) FOR 列 in (…) )AS P

完整文法：

table_source

PIVOT(

聚合函數（value_column）

FOR pivot_column

IN(<column_list>)

)

UNPIVOT用于将列明轉為列值（即列轉行），在SQL Server 2000可以用UNION來實作

UNPIVOT(

value_column

注意：PIVOT、UNPIVOT是SQL Server 2005 的文法，使用需修改資料庫相容級别

 在資料庫屬性->選項->相容級别改為   90

典型執行個體

一、行轉列

1、建立表格

ifobject_id('tb')isnotnulldroptabletb

go

createtabletb(姓名varchar(10),課程varchar(10),分數int)

insertintotbvalues('張三','國文',74)

insertintotbvalues('張三','數學',83)

insertintotbvalues('張三','實體',93)

insertintotbvalues('李四','國文',74)

insertintotbvalues('李四','數學',84)

insertintotbvalues('李四','實體',94)

select*fromtb

姓名       課程       分數

---------- ---------- -----------

張三       國文        74

張三       數學        83

張三       實體        93

李四       國文        74

李四       數學        84

李四       實體        94

2、使用SQL Server 2000靜态SQL

--c

select姓名,

 max(case課程when'國文'then分數else0end)國文,

 max(case課程when'數學'then分數else0end)數學,

 max(case課程when'實體'then分數else0end)實體

fromtb

groupby姓名

姓名       國文        數學        實體

---------- ----------- ----------- -----------

李四        74          84          94

張三        74          83          93

3、使用SQL Server 2000動态SQL

--SQL SERVER 2000動态SQL,指課程不止國文、數學、實體這三門課程。(以下同)

--變量按sql語言順序指派

declare@sqlvarchar(500)

set@sql='select姓名'

select@sql=@sql+',max(case課程when '''+課程+''' then分數else 0 end)['+課程+']'

from(selectdistinct課程fromtb)a--同from tb group by課程，預設按課程名排序

set@sql=@sql+' from tb group by姓名'

exec(@sql)

--使用isnull(),變量先确定動态部分

declare@sqlvarchar(8000)

select@sql=isnull(@sql+',','')+' max(case課程when '''+課程+''' then分數else 0 end) ['+課程+']'

from(selectdistinct課程fromtb)asa      

set@sql='select姓名,'+@sql+' from tb group by姓名'

姓名       數學        實體        國文

李四        84          94          74

張三        83          93          74

4、使用SQL Server 2005靜态SQL

select*fromtb pivot(max(分數)for課程in(國文,數學,實體))a

5、使用SQL Server 2005動态SQL

--使用stuff()

set@sql=''  --初始化變量@sql

select@sql=@sql+','+課程fromtbgroupby課程--變量多值指派

set@sql=stuff(@sql,1,1,'')--去掉首個','

set@sql='select * from tb pivot (max(分數) for課程in ('+@sql+'))a'

--或使用isnull()

–-獲得課程集合

select@sql=isnull(@sql+',','')+課程fromtbgroupby課程           

二、行轉列結果加上總分、平均分

1、使用SQL Server 2000靜态SQL

--SQL SERVER 2000靜态SQL

max(case課程when'國文'then分數else0end)國文,

max(case課程when'數學'then分數else0end)數學,

max(case課程when'實體'then分數else0end)實體,

sum(分數)總分,

cast(avg(分數*1.0)asdecimal(18,2))平均分

姓名       國文        數學        實體        總分        平均分

---------- ----------- ----------- ----------- -----------

李四        74          84          94          252         84.00

張三        74          83          93          250         83.33

2、使用SQL Server 2000動态SQL

--SQL SERVER 2000動态SQL

from(selectdistinct課程fromtb)a

set@sql=@sql+',sum(分數)總分,cast(avg(分數*1.0) as decimal(18,2))      平均分from tb group by姓名'

3、使用SQL Server 2005靜态SQL

selectm.*,n.總分,n.平均分

from

(select*fromtb pivot(max(分數)for課程in(國文,數學,實體))a)m,

(select姓名,sum(分數)總分,cast(avg(分數*1.0)asdecimal(18,2))平均分

groupby姓名)n

wherem.姓名=n.姓名

4、使用SQL Server 2005動态SQL

--

--同select @sql = @sql + ','+課程from (select distinct課程from tb)a

set@sql='select m.* , n.總分,n.平均分from

(select * from (select * from tb) a pivot (max(分數) for課程in ('+@sql+')) b) m ,

(select姓名,sum(分數)總分, cast(avg(分數*1.0) as decimal(18,2))平均分from tb group by姓名) n

where m.姓名= n.姓名'

select@sql=isnull(@sql+',','')+課程fromtbgroupby課程

(select * from (select * from tb) a pivot (max(分數) for課程in ('+

 @sql+')) b) m ,

二、列轉行

createtabletb(姓名varchar(10),國文int,數學int,實體int)

insertintotbvalues('張三',74,83,93)

insertintotbvalues('李四',74,84,94)

張三       74          83          93

--SQL SERVER 2000靜态SQL。

select*from

(

 select姓名,課程='國文',分數=國文fromtb

 unionall

 select姓名,課程='數學',分數=數學fromtb

 select姓名,課程='實體',分數=實體fromtb

) t

orderby姓名,case課程when'國文'then1when'數學'then2when'實體'then3end

姓名       課程 分數

---------- ---- -----------

李四       國文 74

李四       數學 84

李四       實體 94

張三       國文 74

張三       數學 83

張三       實體 93

--SQL SERVER 2000動态SQL。

--調用系統表動态生态。

select@sql=isnull(@sql+' union all ','')+' select姓名, [課程]='

+quotename(Name,'''')+' , [分數] = '+quotename(Name)+' from tb'

fromsyscolumns

whereName!='姓名'andID=object_id('tb')--表名tb，不包含列名為姓名的其他列

orderbycolid

exec(@sql+' order by姓名')

--SQL SERVER 2005動态SQL

select姓名,課程,分數fromtb unpivot (分數for課程in([國文],[數學],[實體])) t

declare@sqlnvarchar(4000)

select@sql=isnull(@sql+',','')+quotename(Name)

whereID=object_id('tb')andNamenotin('姓名')

orderbyColid

set@sql='select姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in('+@sql+'))b'