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