SQL Server 使用 Pivot 和 UnPivot 實現(xiàn)行列轉(zhuǎn)換的問題小結(jié)
對于行列轉(zhuǎn)換的數(shù)據(jù),通常也就是在做報表的時候用的比較多,之前也零零散散的看了一些,今天就來總結(jié)一下。
先創(chuàng)建一個用于演示的臨時表:
create table #temp ( 年份 nvarchar(10) null, 月份 nvarchar(10) null, 數(shù)量 int null ) insert into #temp(年份,月份,數(shù)量) select '2015','1','5645' union select '2015','2','1234' union select '2015','3','7982' union select '2016','1','6465' union select '2016','2','7942' union select '2016','3','8453' union select '2017','1','4653' union select '2017','2','1358' union select '2017','3','7842' select * from #temp
下面來實現(xiàn)一些需求:
需求一,按年份分組,不同的月份為一列。
-- 按年份分組,不同的月份為一列 select t.年份, sum(case t.月份 when '1' then t.數(shù)量 end) '1月份', sum(case t.月份 when '2' then t.數(shù)量 end) '2月份', sum(case t.月份 when '3' then t.數(shù)量 end) '3月份' from #temp t group by t.年份
另外兩種方法:
-- 使用左外連接查詢 select t.年份,t1.數(shù)量 '1月份',t2.數(shù)量 '2月份',t3.數(shù)量 '3月份' from #temp t left join (select 年份,數(shù)量 from #temp where 月份='1') t1 on t.年份=t1.年份 left join (select 年份,數(shù)量 from #temp where 月份='2') t2 on t.年份=t2.年份 left join (select 年份,數(shù)量 from #temp where 月份='3') t3 on t.年份=t3.年份 group by t.年份,t1.數(shù)量,t2.數(shù)量,t3.數(shù)量 -- 使用自連接查詢 select t.年份,t1.數(shù)量 '1月份',t2.數(shù)量 '2月份',t3.數(shù)量 '3月份' from #temp t, (select 年份,數(shù)量 from #temp where 月份='1') t1, (select 年份,數(shù)量 from #temp where 月份='2') t2, (select 年份,數(shù)量 from #temp where 月份='3') t3 where t.年份=t1.年份 and t.年份=t2.年份 and t.年份=t3.年份 group by t.年份,t1.數(shù)量,t2.數(shù)量,t3.數(shù)量
返回的結(jié)果都是一樣的,可以看見這幾種方法都是可以實現(xiàn)的(當(dāng)然,可能還有更多的方法待發(fā)掘),不過比起第一種方法,后面這兩種方法也太低效了吧,比如一年有12個月份的數(shù)據(jù),有個七八年的,那得寫多少個子查詢、表連接的,而且第一種方法也不是我們想要的。那么就需要用到 Pivot 這種方法了。
Pivot 語法:
table_source -- 表名稱,即數(shù)據(jù)源 PIVOT( 聚合函數(shù)(value_column) -- value_column 要轉(zhuǎn)換為 列值 的列名 FOR pivot_column -- pivot_column 指定要轉(zhuǎn)換的列 IN(<column_list>) -- column_list 自定義的目標(biāo)列名 )
因為這里列名不允許指定為數(shù)字,真是無語。。。我重建了一個數(shù)據(jù)結(jié)構(gòu)一模一樣的表。
create table #temp ( Name nvarchar(10) null, Course nvarchar(10) null, Score int null ) insert into #temp(Name,Course,Score) select '小李','語文','88' union select '小李','數(shù)學(xué)','79' union select '小李','英語','85' union select '小明','語文','79' union select '小明','數(shù)學(xué)','89' union select '小明','英語','87' union select '小紅','語文','84' union select '小紅','數(shù)學(xué)','76' union select '小紅','英語','92' select * from #temp go
select Name 姓名, max(case Course when '語文' then Score end) 語文, max(case Course when '數(shù)學(xué)' then Score end) 數(shù)學(xué), max(case Course when '英語' then Score end) 英語, sum(Score) 課程總分, cast(avg(Score) as decimal(18,2)) 課程平均分 from #temp group by Name
使用 Pivot 進行 行轉(zhuǎn)列:
select a.Name 姓名,a.語文,a.數(shù)學(xué),a.英語 from #temp pivot ( max(Score) -- 指定作為轉(zhuǎn)換的列的值 的列名 for Course -- 指定要轉(zhuǎn)換的列的列名 in(語文,數(shù)學(xué),英語) -- 自定義的目標(biāo)列名,即要轉(zhuǎn)換列的不同的值作為列 )
select a.Name 姓名,a.語文,a.數(shù)學(xué),a.英語,b.SumScore 課程總分,b.AvgScore 課程平均分 from #temp pivot ( max(Score) -- 指定作為轉(zhuǎn)換的列的值 的列名 for Course -- 指定要轉(zhuǎn)換的列的列名 in(語文,數(shù)學(xué),英語) -- 自定義的目標(biāo)列名,即要轉(zhuǎn)換列的不同的值作為列 )a, ( select t.Name,sum(t.Score) SumScore,cast(avg(t.Score) as decimal(18,2)) AvgScore from #temp t group by t.Name )b where a.Name=b.Name
UnPivot 語法:
table_source -- 表名稱,即數(shù)據(jù)源 UNPIVOT( value_column -- value_column 要轉(zhuǎn)換為 行值 的列名 FOR pivot_column -- pivot_column 指定要轉(zhuǎn)換為指定的列 IN(<column_list>) -- column_list 目標(biāo)列名 )
create table #temp ( Name nvarchar(10) null, Chinese int null, Math int null, English int null ) insert into #temp(Name,Chinese,Math,English) select '小李','88','79','85' union select '小明','79','89','87' union select '小紅','84','76','92' select * from #temp go
select t.Name 姓名,t.Course 課程,t.Score 分?jǐn)?shù) from (select t.Name,Course='Chinese',Score=Chinese from #temp t union all select t.Name,Course='Math',Score=Math from #temp t union all select t.Name,Course='English',Score=English from #temp t) t order by t.Name,t.Course
select t.Name 姓名,t.Course 課程,t.Score 分?jǐn)?shù) from (select t.Name,'Chinese' Course,Chinese Score from #temp t union all select t.Name,'Math',Math from #temp t union all select t.Name,'English',English from #temp t) t order by t.Name,t.Course
使用 UnPivot 進行 列轉(zhuǎn)行:
select t.Name 姓名,t.Course 課程,t.Score 分?jǐn)?shù) from #temp unpivot ( Score for Course in(Chinese,Math,English) )
到此這篇關(guān)于SQL Server 使用 Pivot 和 UnPivot 實現(xiàn)行列轉(zhuǎn)換的文章就介紹到這了,更多相關(guān)SQL Server行列轉(zhuǎn)換內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
Microsoft SQLServer的版本區(qū)別及選擇
Microsoft SQLServer的版本區(qū)別及選擇...2007-02-02SQL Server誤區(qū)30日談 第21天 數(shù)據(jù)損壞可以通過重啟SQL Server來修復(fù)
SQL Server中沒有任何一項操作可以修復(fù)數(shù)據(jù)損壞。損壞的頁當(dāng)然需要通過某種機制進行修復(fù)或是恢復(fù)-但絕不是通過重啟動SQL Server,Windows亦或是分離附加數(shù)據(jù)庫2013-01-01SqlServer備份數(shù)據(jù)庫的4種方式介紹
這篇文章主要介紹了SqlServer備份數(shù)據(jù)庫的4種方式介紹,本文講解了用sqlserver的維護計劃、通過腳本+作業(yè)的方式備份數(shù)據(jù)庫(非xp_cmdshell和xp_cmdshell)、用powershell調(diào)用sqlcmd來執(zhí)行備份命令幾種方式,需要的朋友可以參考下2015-02-02Sql Server中通過sql命令獲取cpu占用及產(chǎn)生鎖的sql
這篇文章主要介紹了Sql Server中通過sql命令獲取cpu占用及產(chǎn)生鎖的sql,需要的朋友可以參考下2022-10-10Sql Server中存儲過程中輸入和輸出參數(shù)(簡單實例 一看就懂)
Sql Server中存儲過程中輸入和輸出參數(shù)(簡單實例,一看就懂),方便需要的朋友2012-10-10