您好,登錄后才能下訂單哦!
這篇“SQL Server中怎么使用Pivot和UnPivot實(shí)現(xiàn)行列轉(zhuǎn)換”文章的知識(shí)點(diǎn)大部分人都不太理解,所以小編給大家總結(jié)了以下內(nèi)容,內(nèi)容詳細(xì),步驟清晰,具有一定的借鑒價(jià)值,希望大家閱讀完這篇文章能有所收獲,下面我們一起來看看這篇“SQL Server中怎么使用Pivot和UnPivot實(shí)現(xiàn)行列轉(zhuǎn)換”文章吧。
先創(chuàng)建一個(gè)用于演示的臨時(shí)表:
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
下面來實(shí)現(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é)果都是一樣的,可以看見這幾種方法都是可以實(shí)現(xiàn)的(當(dāng)然,可能還有更多的方法待發(fā)掘),不過比起第一種方法,后面這兩種方法也太低效了吧,比如一年有12個(gè)月份的數(shù)據(jù),有個(gè)七八年的,那得寫多少個(gè)子查詢、表連接的,而且第一種方法也不是我們想要的。那么就需要用到 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)列名 )
因?yàn)檫@里列名不允許指定為數(shù)字,真是無語。。。我重建了一個(gè)數(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 進(jìn)行 行轉(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 進(jìn)行 列轉(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實(shí)現(xiàn)行列轉(zhuǎn)換”這篇文章的內(nèi)容,相信大家都有了一定的了解,希望小編分享的內(nèi)容對(duì)大家有幫助,若想了解更多相關(guān)的知識(shí)內(nèi)容,請(qǐng)關(guān)注億速云行業(yè)資訊頻道。
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。