SQL行轉列（學習記錄）

原始資料

Id	Year	Month	Money
1	2020	1		100
2	2020	2		200
3	2020	3		300
4	2020	4		400
5	2020	5		500
6	2020	6		600
7	2021	1		100
8	2021	2		200
9	2021	3		300
10	2021	4		400
11	2021	5		500
12	2021	6		600
           

轉換後資料

Year	Mon1	Mon2	Mon3	Mon4	Mon5	Mon6
2020	100		200		300		400		500		600
2021	100		200		300		400		500		600
           

資料初始化

use MyDB
go

if OBJECT_ID('Sale','U') > 0
	drop table Sale

create table Sale(
	Id int identity(1,1) not null primary key,
	[Year] int not null,
	[Month] int not null,
	[Money] int not null
)

insert into Sale values(2020, 1, 100)
insert into Sale values(2020, 2, 200)
insert into Sale values(2020, 3, 300)
insert into Sale values(2020, 4, 400)
insert into Sale values(2020, 5, 500)
insert into Sale values(2020, 6, 600)
insert into Sale values(2021, 1, 100)
insert into Sale values(2021, 2, 200)
insert into Sale values(2021, 3, 300)
insert into Sale values(2021, 4, 400)
insert into Sale values(2021, 5, 500)
insert into Sale values(2021, 6, 600)

select * from Sale
           

case when函數實作（SqlServer 2017）

select Year,
sum(case [Month] when 1 then [Money] else 0 end) Mon1,
sum(case [Month] when 2 then [Money] else 0 end) Mon2,
sum(case [Month] when 3 then [Money] else 0 end) Mon3,
sum(case [Month] when 4 then [Money] else 0 end) Mon4,
sum(case [Month] when 5 then [Money] else 0 end) Mon5,
sum(case [Month] when 6 then [Money] else 0 end) Mon6
from Sale group by Year
           

pivot函數實作（SqlServer 2017）

有多餘的列且該列資料各不相同，使用表中所有列進行轉換，無法達到預期效果。

SELECT Year,
         [1] Mon1,
         [2] Mon2,
         [3] Mon3,
         [4] Mon4,
         [5] Mon5,
         [6] Mon6
FROM Sale 
pivot(sum([Money]) for [Month] IN ([1], [2], [3], [4], [5], [6])) tb

> 結果如下
Year	Mon1	Mon2	Mon3	Mon4	Mon5	Mon6
2020	100		NULL	NULL	NULL	NULL	NULL
2020	NULL	200		NULL	NULL	NULL	NULL
2020	NULL	NULL	300		NULL	NULL	NULL
2020	NULL	NULL	NULL	400		NULL	NULL
2020	NULL	NULL	NULL	NULL	500		NULL
2020	NULL	NULL	NULL	NULL	NULL	600
2021	100		NULL	NULL	NULL	NULL	NULL
2021	NULL	200		NULL	NULL	NULL	NULL
2021	NULL	NULL	300		NULL	NULL	NULL
2021	NULL	NULL	NULL	400		NULL	NULL
2021	NULL	NULL	NULL	NULL	500		NULL
2021	NULL	NULL	NULL	NULL	NULL	600
           

篩選表中需要的列，再進行轉換。

SELECT Year,
         [1] Mon1,
         [2] Mon2,
         [3] Mon3,
         [4] Mon4,
         [5] Mon5,
         [6] Mon6
FROM 
	-- 篩選需要的列 START
    (SELECT [Year],
         [Month],
         [Money]
    FROM Sale ) SaleFilter 
	-- 篩選需要的列 END
    pivot( sum([Money]) for [Month] IN ([1], [2], [3], [4], [5], [6]) ) tb 

> 結果如下
Year	Mon1	Mon2	Mon3	Mon4	Mon5	Mon6
2020	100		200		300		400		500		600
2021	100		200		300		400		500		600