PIVOT用于將列值旋轉為列名（即行轉列），在SQL Server2000可以用聚合函數配合CASE語句實現

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

完整語法：

table_source

PIVOT(

聚合函數（value_column）

FOR pivot_column

IN()

)

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

完整語法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN()

)

注意：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)

go

select*fromtb

go

姓名課程分數

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

張三語文74

張三數學83

張三物理93

李四語文74

李四數學84

李四物理94

2、使用SQLServer 2000靜態SQL

--c

select姓名,

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

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

max(case課程when'物理'then分數else0end)物理

fromtb

groupby姓名

姓名語文數學物理

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

李四748494

張三748393

3、使用SQLServer 2000動態SQL

--SQLSERVER 2000動態SQL,指課程不止語文、數學、物理這三門課程。(以下同)

--變量按sql語言順序賦值

declare@sqlvarchar(500)

set@sql='select姓名'

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

from(selectdistinct課程fromtb)a--同fromtb group by課程，默認按課程名排序

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

exec(@sql)

--使用isnull(),變量先確定動態部分

declare@sqlvarchar(8000)

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

from(selectdistinct課程fromtb)asa

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

exec(@sql)

姓名數學物理語文

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

李四849474

張三839374

4、使用SQLServer 2005靜態SQL

select*fromtbpivot(max(分數)for課程in(語文,數學,物理))a

5、使用SQL Server2005動態SQL

--使用stuff()

declare@sqlvarchar(8000)

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

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

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

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

exec(@sql)

--或使用isnull()

declare@sqlvarchar(8000)

–-獲得課程集合

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

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

exec(@sql)

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

1、使用SQLServer 2000靜態SQL

--SQLSERVER 2000靜態SQL

select姓名,

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

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

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

sum(分數)總分,

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

fromtb

groupby姓名

姓名語文數學物理總分平均分

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

李四74849425284.00

張三74839325083.33

2、使用SQLServer 2000動態SQL

--SQLSERVER 2000動態SQL

declare@sqlvarchar(500)

set@sql='select姓名'

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

from(selectdistinct課程fromtb)a

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

exec(@sql)