SQL Server 使用 Pivot 和 UnPivot 實現行列轉換的問題小結
對於行列轉換的數據，通常也就是在做報表的時候用的比較多，之前也零零散散的看瞭一些，今天就來總結一下。
先創建一個用於演示的臨時表：
create table #temp
(
年份 nvarchar(10) null,
月份 nvarchar(10) null,
數量 int null
)
insert into #temp(年份,月份,數量)
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
下面來實現一些需求：
需求一，按年份分組，不同的月份為一列。
-- 按年份分組，不同的月份為一列 select t.年份, sum(case t.月份 when '1' then t.數量 end) '1月份', sum(case t.月份 when '2' then t.數量 end) '2月份', sum(case t.月份 when '3' then t.數量 end) '3月份' from #temp t group by t.年份
另外兩種方法：
-- 使用左外連接查詢 select t.年份,t1.數量 '1月份',t2.數量 '2月份',t3.數量 '3月份' from #temp t left join (select 年份,數量 from #temp where 月份='1') t1 on t.年份=t1.年份 left join (select 年份,數量 from #temp where 月份='2') t2 on t.年份=t2.年份 left join (select 年份,數量 from #temp where 月份='3') t3 on t.年份=t3.年份 group by t.年份,t1.數量,t2.數量,t3.數量 -- 使用自連接查詢 select t.年份,t1.數量 '1月份',t2.數量 '2月份',t3.數量 '3月份' from #temp t, (select 年份,數量 from #temp where 月份='1') t1, (select 年份,數量 from #temp where 月份='2') t2, (select 年份,數量 from #temp where 月份='3') t3 where t.年份=t1.年份 and t.年份=t2.年份 and t.年份=t3.年份 group by t.年份,t1.數量,t2.數量,t3.數量
返回的結果都是一樣的，可以看見這幾種方法都是可以實現的（當然，可能還有更多的方法待發掘），不過比起第一種方法，後面這兩種方法也太低效瞭吧，比如一年有12個月份的數據，有個七八年的，那得寫多少個子查詢、表連接的，而且第一種方法也不是我們想要的。那麼就需要用到 Pivot 這種方法瞭。
Pivot 語法：
table_source -- 表名稱，即數據源
PIVOT(
聚合函數（value_column） -- value_column 要轉換為 列值 的列名
FOR pivot_column -- pivot_column 指定要轉換的列
IN(<column_list>) -- column_list 自定義的目標列名
)
因為這裡列名不允許指定為數字，真是無語。。。我重建瞭一個數據結構一模一樣的表。
create table #temp
(
Name nvarchar(10) null,
Course nvarchar(10) null,
Score int null
)
insert into #temp(Name,Course,Score)
select '小李','語文','88' union
select '小李','數學','79' union
select '小李','英語','85' union
select '小明','語文','79' union
select '小明','數學','89' union
select '小明','英語','87' union
select '小紅','語文','84' union
select '小紅','數學','76' union
select '小紅','英語','92'
select * from #temp
go
select Name 姓名, max(case Course when '語文' then Score end) 語文, max(case Course when '數學' then Score end) 數學, max(case Course when '英語' then Score end) 英語, sum(Score) 課程總分, cast(avg(Score) as decimal(18,2)) 課程平均分 from #temp group by Name
使用 Pivot 進行 行轉列：
select a.Name 姓名,a.語文,a.數學,a.英語
from #temp
pivot
(
max(Score) -- 指定作為轉換的列的值 的列名
for Course -- 指定要轉換的列的列名
in(語文,數學,英語) -- 自定義的目標列名，即要轉換列的不同的值作為列
)
select a.Name 姓名,a.語文,a.數學,a.英語,b.SumScore 課程總分,b.AvgScore 課程平均分
from #temp
pivot
(
max(Score) -- 指定作為轉換的列的值 的列名
for Course -- 指定要轉換的列的列名
in(語文,數學,英語) -- 自定義的目標列名，即要轉換列的不同的值作為列
)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 -- 表名稱，即數據源
UNPIVOT(
value_column -- value_column 要轉換為 行值 的列名
FOR pivot_column -- pivot_column 指定要轉換為指定的列
IN(<column_list>) -- column_list 目標列名
)
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 分數 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 分數 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 進行 列轉行：
select t.Name 姓名,t.Course 課程,t.Score 分數
from #temp
unpivot
(
Score for Course
in(Chinese,Math,English)
)
到此這篇關於SQL Server 使用 Pivot 和 UnPivot 實現行列轉換的文章就介紹到這瞭,更多相關SQL Server行列轉換內容請搜索WalkonNet以前的文章或繼續瀏覽下面的相關文章希望大傢以後多多支持WalkonNet！