原文:
sql 行轉(zhuǎn)列總結(jié)
?
PIVOT UNPIVOT的用法
PIVOT用于將列值旋轉(zhuǎn)為列名（即行轉(zhuǎn)列），在SQL Server 2000可以用聚合函數(shù)配合CASE語句實現(xiàn)
PIVOT的一般語法是：PIVOT(聚合函數(shù)(列) FOR 列 in (…) )AS P
完整語法：
table_source
PIVOT(
聚合函數(shù)（value_column）
FOR pivot_column
IN(<column_list>)
)
UNPIVOT用于將列明轉(zhuǎn)為列值（即列轉(zhuǎn)行），在SQL Server 2000可以用UNION來實現(xiàn)
完整語法：
table_source
UNPIVOT(
value_column
FOR pivot_column
IN(<column_list>)
)
注意：PIVOT、UNPIVOT是SQL Server 2005的語法，使用需修改數(shù)據(jù)庫兼容級別
在數(shù)據(jù)庫屬性->選項->兼容級別改為 90
典型實例
一、行轉(zhuǎn)列
1、建立表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10),課程varchar(10),分數(shù)int)
insertintotbvalues('張三','語文',74)
insertintotbvalues('張三','數(shù)學',83)
insertintotbvalues('張三','物理',93)
insertintotbvalues('李四','語文',74)
insertintotbvalues('李四','數(shù)學',84)
insertintotbvalues('李四','物理',94)
go
select*fromtb
go
姓名 課程 分數(shù)
---------- ---------- -----------
張三 語文 74
張三 數(shù)學 83
張三 物理 93
李四 語文 74
李四 數(shù)學 84
李四 物理 94
2、使用SQL Server 2000靜態(tài)SQL
--c
select姓名,
max(case課程when'語文'then分數(shù)else0end)語文,
max(case課程when'數(shù)學'then分數(shù)else0end)數(shù)學,
max(case課程when'物理'then分數(shù)else0end)物理
fromtb
groupby姓名
姓名 語文 數(shù)學 物理
---------- ----------- ----------- -----------
李四 74 84 94
張三 74 83 93
3、使用SQL Server 2000動態(tài)SQL
--SQL SERVER 2000動態(tài)SQL,指課程不止語文、數(shù)學、物理這三門課程。(以下同)
--變量按sql語言順序賦值
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case課程when '''+課程+''' then分數(shù)else 0 end)['+課程+']'
from(selectdistinct課程fromtb)a--同from tb group by課程，默認按課程名排序
set@sql=@sql+' from tb group by姓名'
exec(@sql)
--使用isnull(),變量先確定動態(tài)部分
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+' max(case課程when '''+課程+''' then分數(shù)else 0 end) ['+課程+']'
from(selectdistinct課程fromtb)asa
set@sql='select姓名,'+@sql+' from tb group by姓名'
exec(@sql)
姓名 數(shù)學 物理 語文
---------- ----------- ----------- -----------
李四 84 94 74
張三 83 93 74
4、使用SQL Server 2005靜態(tài)SQL
select*fromtb pivot(max(分數(shù))for課程in(語文,數(shù)學,物理))a
5、使用SQL Server 2005動態(tài)SQL
--使用stuff()
declare@sqlvarchar(8000)
set@sql='' --初始化變量@sql
select@sql=@sql+','+課程fromtbgroupby課程--變量多值賦值
set@sql=stuff(@sql,1,1,'')--去掉首個','
set@sql='select * from tb pivot (max(分數(shù)) for課程in ('+@sql+'))a'
exec(@sql)
--或使用isnull()
declare@sqlvarchar(8000)
–-獲得課程集合
select@sql=isnull(@sql+',','')+課程fromtbgroupby課程
set@sql='select * from tb pivot (max(分數(shù)) for課程in ('+@sql+'))a'
exec(@sql)
二、行轉(zhuǎn)列結(jié)果加上總分、平均分
1、使用SQL Server 2000靜態(tài)SQL
--SQL SERVER 2000靜態(tài)SQL
select姓名,
max(case課程when'語文'then分數(shù)else0end)語文,
max(case課程when'數(shù)學'then分數(shù)else0end)數(shù)學,
max(case課程when'物理'then分數(shù)else0end)物理,
sum(分數(shù))總分,
cast(avg(分數(shù)*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名
姓名 語文 數(shù)學 物理 總分 平均分
---------- ----------- ----------- ----------- -----------
李四 74 84 94 252 84.00
張三 74 83 93 250 83.33
2、使用SQL Server 2000動態(tài)SQL
--SQL SERVER 2000動態(tài)SQL
declare@sqlvarchar(500)
set@sql='select姓名'
select@sql=@sql+',max(case課程when '''+課程+''' then分數(shù)else 0 end)['+課程+']'
from(selectdistinct課程fromtb)a
set@sql=@sql+',sum(分數(shù))總分,cast(avg(分數(shù)*1.0) as decimal(18,2)) 平均分from tb group by姓名'
exec(@sql)
3、使用SQL Server 2005靜態(tài)SQL
selectm.*,n.總分,n.平均分
from
(select*fromtb pivot(max(分數(shù))for課程in(語文,數(shù)學,物理))a)m,
(select姓名,sum(分數(shù))總分,cast(avg(分數(shù)*1.0)asdecimal(18,2))平均分
fromtb
groupby姓名)n
wherem.姓名=n.姓名
4、使用SQL Server 2005動態(tài)SQL
--使用stuff()
--
declare@sqlvarchar(8000)
set@sql='' --初始化變量@sql
select@sql=@sql+','+課程fromtbgroupby課程--變量多值賦值
--同select @sql = @sql + ','+課程from (select distinct課程from tb)a
set@sql=stuff(@sql,1,1,'')--去掉首個','
set@sql='select m.* , n.總分,n.平均分from
(select * from (select * from tb) a pivot (max(分數(shù)) for課程in ('+@sql+')) b) m ,
(select姓名,sum(分數(shù))總分, cast(avg(分數(shù)*1.0) as decimal(18,2))平均分from tb group by姓名) n
where m.姓名= n.姓名'
exec(@sql)
--或使用isnull()
declare@sqlvarchar(8000)
select@sql=isnull(@sql+',','')+課程fromtbgroupby課程
set@sql='select m.* , n.總分,n.平均分from
(select * from (select * from tb) a pivot (max(分數(shù)) for課程in ('+
@sql+')) b) m ,
(select姓名,sum(分數(shù))總分, cast(avg(分數(shù)*1.0) as decimal(18,2))平均分from tb group by姓名) n
where m.姓名= n.姓名'
exec(@sql)
二、列轉(zhuǎn)行
1、建立表格
ifobject_id('tb')isnotnulldroptabletb
go
createtabletb(姓名varchar(10),語文int,數(shù)學int,物理int)
insertintotbvalues('張三',74,83,93)
insertintotbvalues('李四',74,84,94)
go
select*fromtb
go
姓名 語文 數(shù)學 物理
---------- ----------- ----------- -----------
張三 74 83 93
李四 74 84 94
2、使用SQL Server 2000靜態(tài)SQL
--SQL SERVER 2000靜態(tài)SQL。
select*from
(
select姓名,課程='語文',分數(shù)=語文fromtb
unionall
select姓名,課程='數(shù)學',分數(shù)=數(shù)學fromtb
unionall
select姓名,課程='物理',分數(shù)=物理fromtb
) t
orderby姓名,case課程when'語文'then1when'數(shù)學'then2when'物理'then3end
姓名 課程 分數(shù)
---------- ---- -----------
李四 語文 74
李四 數(shù)學 84
李四 物理 94
張三 語文 74
張三 數(shù)學 83
張三 物理 93
2、使用SQL Server 2000動態(tài)SQL
--SQL SERVER 2000動態(tài)SQL。
--調(diào)用系統(tǒng)表動態(tài)生態(tài)。
declare@sqlvarchar(8000)
select@sql=isnull(@sql+' union all ','')+' select姓名, [課程]='
+quotename(Name,'''')+' , [分數(shù)] = '+quotename(Name)+' from tb'
fromsyscolumns
whereName!='姓名'andID=object_id('tb')--表名tb，不包含列名為姓名的其他列
orderbycolid
exec(@sql+' order by姓名')
go
3、使用SQL Server 2005靜態(tài)SQL
--SQL SERVER 2005動態(tài)SQL
select姓名,課程,分數(shù)fromtb unpivot (分數(shù)for課程in([語文],[數(shù)學],[物理])) t
4、使用SQL Server 2005動態(tài)SQL
--SQL SERVER 2005動態(tài)SQL
declare@sqlnvarchar(4000)
select@sql=isnull(@sql+',','')+quotename(Name)
fromsyscolumns
whereID=object_id('tb')andNamenotin('姓名')
orderbyColid
set@sql='select姓名,[課程],[分數(shù)] from tb unpivot ([分數(shù)] for [課程] in('+@sql+'))b'
exec(@sql)
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號聯(lián)系： 360901061
您的支持是博主寫作最大的動力，如果您喜歡我的文章，感覺我的文章對您有幫助，請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點擊下面給點支持吧，站長非常感激您！手機微信長按不能支付解決辦法：請將微信支付二維碼保存到相冊，切換到微信，然后點擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對您有幫助就好】元
