SQL Server中行列轉置方法

標籤：style   blog   io   ar   os   使用   sp   for   strong   

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來實現

完整文法：

table_source

UNPIVOT(

value_column

FOR pivot_column

IN(<column_list>)

)

 

注意：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、使用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語言順序賦值

[email protected](500)

[email protected]=‘select姓名‘

[email protected][email protected]+‘,max(case課程when ‘‘‘+課程+‘‘‘ then分數else 0 end)[‘+課程+‘]‘

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

[email protected][email protected]+‘ from tb group by姓名‘

exec(@sql)

 

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

[email protected](8000)

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

from(selectdistinct課程fromtb)asa

[email protected]=‘select姓名,‘[email protected]+‘ from tb group by姓名‘

exec(@sql)

姓名 數學 物理 語文

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

李四 84 94 74

張三 83 93 74

 

4、使用SQL Server 2005靜態SQL

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

 

5、使用SQL Server 2005動態SQL

--使用stuff()

[email protected](8000)

[email protected]=‘‘ --初始設定變數@sql

[email protected][email protected]+‘,‘+課程fromtbgroupby課程--變數多值賦值

[email protected]=stuff(@sql,1,1,‘‘)--去掉首個‘,‘

[email protected]=‘select * from tb pivot (max(分數) for課程in (‘[email protected]+‘))a‘

exec(@sql)

 

--或使用isnull()

[email protected](8000)

–-獲得課程集合

[email protected]=isnull(@sql+‘,‘,‘‘)+課程fromtbgroupby課程

[email protected]=‘select * from tb pivot (max(分數) for課程in (‘[email protected]+‘))a‘

exec(@sql)

 

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

1、使用SQL Server 2000靜態SQL

--SQL SERVER 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姓名

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

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

李四 74 84 94 252 84.00

張三 74 83 93 250 83.33

 

2、使用SQL Server 2000動態SQL

--SQL SERVER 2000動態SQL

[email protected](500)

[email protected]=‘select姓名‘

[email protected][email protected]+‘,max(case課程when ‘‘‘+課程+‘‘‘ then分數else 0 end)[‘+課程+‘]‘

from(selectdistinct課程fromtb)a

[email protected][email protected]+‘,sum(分數)總分,cast(avg(分數*1.0) as decimal(18,2)) 平均分from tb group by姓名‘

exec(@sql)

 

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))平均分

fromtb

groupby姓名)n

wherem.姓名=n.姓名

 

4、使用SQL Server 2005動態SQL

--使用stuff()

--

[email protected](8000)

[email protected]=‘‘ --初始設定變數@sql

[email protected][email protected]+‘,‘+課程fromtbgroupby課程--變數多值賦值

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

[email protected]=stuff(@sql,1,1,‘‘)--去掉首個‘,‘

[email protected]=‘select m.* , n.總分,n.平均分from

(select * from (select * from tb) a pivot (max(分數) for課程in (‘[email protected]+‘)) b) m ,

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

where m.姓名= n.姓名‘

exec(@sql)

 

--或使用isnull()

[email protected](8000)

[email protected]=isnull(@sql+‘,‘,‘‘)+課程fromtbgroupby課程

[email protected]=‘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.姓名‘

exec(@sql)

 

二、列轉行

1、建立表格

ifobject_id(‘tb‘)isnotnulldroptabletb

go

createtabletb(姓名varchar(10),語文int,數學int,物理int)

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

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

go

select*fromtb

go

姓名 語文 數學 物理

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

張三 74 83 93

李四 74 84 94

 

2、使用SQL Server 2000靜態SQL

--SQL SERVER 2000靜態SQL。

select*from

(

select姓名,課程=‘語文‘,分數=語文fromtb

unionall

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

unionall

select姓名,課程=‘物理‘,分數=物理fromtb

) t

orderby姓名,case課程when‘語文‘then1when‘數學‘then2when‘物理‘then3end

姓名 課程 分數

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

李四 語文 74

李四 數學 84

李四 物理 94

張三 語文 74

張三 數學 83

張三 物理 93

 

2、使用SQL Server 2000動態SQL

--SQL SERVER 2000動態SQL。

--調用系統資料表動態生態。

[email protected](8000)

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

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

fromsyscolumns

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

orderbycolid

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

go

 

3、使用SQL Server 2005靜態SQL

--SQL SERVER 2005動態SQL

select姓名,課程,分數fromtb unpivot (分數for課程in([語文],[數學],[物理])) t

 

4、使用SQL Server 2005動態SQL

--SQL SERVER 2005動態SQL

[email protected](4000)

[email protected]=isnull(@sql+‘,‘,‘‘)+quotename(Name)

fromsyscolumns

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

orderbyColid

[email protected]=‘select姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in(‘[email protected]+‘))b‘

exec(@sql)

SQL Server中行列轉置方法