原文:
sql 行轉列總結
?
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來實現 完整語法: 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語言順序賦值 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 74 4、使用SQL Server 2005靜態SQL select*fromtb pivot(max(分數)for課程in(語文,數學,物理))a 5、使用SQL Server 2005動態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(分數) 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))平均分 fromtb groupby姓名 姓名 語文 數學 物理 總分 平均分 ---------- ----------- ----------- ----------- ----------- 李四 74 84 94 252 84.00 張三 74 83 93 250 83.33 2、使用SQL Server 2000動態SQL --SQL SERVER 2000動態SQL declare@sqlvarchar(500) set@sql='select姓名' select@sql=@sql+',max(case課程when '''+課程+''' then分數else 0 end)['+課程+']' from(selectdistinct課程fromtb)a set@sql=@sql+',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() -- 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(分數) 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) --或使用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姓名) 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。 --調用系統表動態生態。 declare@sqlvarchar(8000) 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姓名') 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 declare@sqlnvarchar(4000) select@sql=isnull(@sql+',','')+quotename(Name) fromsyscolumns whereID=object_id('tb')andNamenotin('姓名') orderbyColid set@sql='select姓名,[課程],[分數] from tb unpivot ([分數] for [課程] in('+@sql+'))b' exec(@sql)
?
更多文章、技術交流、商務合作、聯系博主
微信掃碼或搜索:z360901061

微信掃一掃加我為好友
QQ號聯系: 360901061
您的支持是博主寫作最大的動力,如果您喜歡我的文章,感覺我的文章對您有幫助,請用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧,狠狠點擊下面給點支持吧,站長非常感激您!手機微信長按不能支付解決辦法:請將微信支付二維碼保存到相冊,切換到微信,然后點擊微信右上角掃一掃功能,選擇支付二維碼完成支付。
【本文對您有幫助就好】元
