SQL行轉列匯總PIVOT用于將列值旋轉為列名（即行轉列），在SQL Server 2000可以用聚合函數配合CASE語句實現PIVOT的一般語法是：PIVOT(聚合函數(列) FOR 列 in (…) )AS P完整語法：table_sourcePIVOT(聚合函數（value_column）FOR pivot_columnIN(<column_list>))UNPIVOT用于將列明轉為列值（即列轉行），在SQL Server 2000可以用UNION來實現完整語法：table_sourceUNPIVOT(value_columnFOR pivot_columnIN(<column_list>))注意：PIVOT、UNPIVOT是SQL Server 2005 的語法，使用需修改數據庫兼容級別在數據庫屬性->選項->兼容級別改為 90典型實例一、行轉列1、建立表格ifobject_id('tb')isnotnulldroptabletbgocreatetabletb(姓名varchar(10),課程varchar(10),分數int)insertintotbvalues('張三','語文',74)insertintotbvalues('張三','數學',83)insertintotbvalues('張三','物理',93)insertintotbvalues('李四','語文',74)insertintotbvalues('李四','數學',84)insertintotbvalues('李四','物理',94)goselect*fromtbgo姓名 課程 分數---------- ---------- ----------- 張三 語文 74張三 數學 83張三 物理 93李四 語文 74李四 數學 84李四 物理 942、使用SQL Server 2000靜態SQL--c select姓名,max(case課程when'語文'then分數else0end)語文,max(case課程when'數學'then分數else0end)數學,max(case課程when'物理'then分數else0end)物理fromtbgroupby姓名姓名 語文 數學 物理---------- ----------- ----------- ----------- 李四 74 84 94張三 74 83 933、使用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姓名'exec(@sql)姓名 數學 物理 語文---------- ----------- ----------- ----------- 李四 84 94 74張三 83 93 744、使用SQL Server 2005靜態SQLselect*fromtb pivot(max(分數)for課程in(語文,數學,物理))a5、使用SQL Server 2005動態SQL--使用stuff()declare@sqlvarchar(8000)set@sql='' --初始化變量@sqlselect@sql=@sql+','+課程fromtbgroupby課程--變量多值賦值set@sql=stuff(@sql,1,1,'')--去掉首個','set@sql='select * from tb pivot (max(分數) for課程in ('+@sql+'))a'exec(@sql)--或使用isnull()declare@sqlvarchar(8000)–-獲得課程集合select@sql=isnull(@sql+',','')+課程fromtbgroupby課程 set@sql='select * from tb pivot (max(分數) for課程in ('+@sql+'))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))平均分fromtbgroupby姓名姓名 語文 數學 物理 總分 平均分---------- ----------- ----------- ----------- ----------- 李四 74 84 94 252 84.00張三 74 83 93 250 83.332、使用SQL Server 2000動態SQL--SQL SERVER 2000動態SQLdeclare@sqlvarchar(500)set@sql='select姓名'select@sql=@sql+',max(case課程when '''+課程+''' then分數else 0 end)['+課程+']'from(selectdistinct課程fromtb)aset@sql=@sql+',sum(分數)總分,cast(avg(分數*1.0) as decimal(18,2)) 平均分from tb group by姓名'exec(@sql)3、使用SQL Server 2005靜態SQLselectm.*,n.總分,n.平均分from(select*fromtb pivot(max(分數)for課程in(語文,數學,物理))a)m,(select姓名,sum(分數)總分,cast(avg(分數*1.0)asdecimal(18,2))平均分fromtbgroupby姓名)nwherem.姓名=n.姓名4、使用SQL Server 2005動態SQL--使用stuff()-- declare@sqlvarchar(8000)set@sql='' --初始化變量@sqlselect@sql=@sql+','+課程fromtbgroupby課程--變量多值賦值--同select @sql = @sql + ','+課程from (select distinct課程from tb)aset@sql=stuff(@sql,1,1,'')--去掉首個','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姓名) nwhere 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(分數) for課程in ('+@sql+')) b) m ,(select姓名,sum(分數)總分, cast(avg(分數*1.0) as decimal(18,2))平均分from tb group by姓名) nwhere m.姓名= n.姓名'exec(@sql)二、列轉行1、建立表格ifobject_id('tb')isnotnulldroptabletbgocreatetabletb(姓名varchar(10),語文int,數學int,物理int)insertintotbvalues('張三',74,83,93)insertintotbvalues('李四',74,84,94)goselect*fromtbgo姓名 語文 數學 物理---------- ----------- ----------- ----------- 張三 74 83 93李四 74 84 942、使用SQL Server 2000靜態SQL--SQL SERVER 2000靜態SQL。select*from(select姓名,課程='語文',分數=語文fromtbunionallselect姓名,課程='數學',分數=數學fromtbunionallselect姓名,課程='物理',分數=物理fromtb) torderby姓名,case課程when'語文'then1when'數學'then2when'物理'then3end姓名 課程 分數---------- ---- ----------- 李四 語文 74李四 數學 84李四 物理 94張三 語文 74張三 數學 83張三 物理 932、使用SQL Server 2000動態SQL--SQL SERVER 2000動態SQL。--調用系統表動態生態。declare@sqlvarchar(8000)select@sql=isnull(@sql+' union all ','')+' select姓名, [課程]='+quotename(Name,'''')+' , [分數] = '+quotename(Name)+' from tb'fromsyscolumnswhereName!='姓名'andID=object_id('tb')--表名tb，不包含列名為姓名的其他列 orderbycolidexec(@sql+' order by姓名')go3、使用SQL Server 2005靜態SQL--SQL SERVER 2005動態SQL select姓名,課程,分數fromtb unpivot (分數for課程in([語文],[數學],[物理])) t4、使用SQL Server 2005動態SQL--SQL SERVER 2005動態SQLdeclare@sqlnvarchar(4000)select@sql=isnull(@sql+',','')+quotename(Name)fromsyscolumnswhereID=object_id('tb')andNamenotin('姓名')orderbyColidset@sql='select姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in('+@sql+'))b'exec(@sql)

?

轉載于:https://www.cnblogs.com/wzq806341010/p/3785795.html

總結

以上是生活随笔為你收集整理的Sql 语句收集——行转列的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。