SQL行转列（学习记录）

最新推荐文章于 2024-09-13 23:50:45 发布

offer绝缘机

最新推荐文章于 2024-09-13 23:50:45 发布

阅读量54

点赞数

文章标签： sql sqlserver

本文链接：https://blog.csdn.net/weixin_44971857/article/details/115791789

版权

原始数据

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