简介
如何在SQL Server中实现行专列的查询
实战操作
创建一张临时表 #amount
create table #amount
(
Year Nvarchar(10),
Quarter Nvarchar(10),
Amount float
)
insert into #amount
select '2019','Q1',1.1
union all
select '2019','Q2',1.2
union all
select '2019','Q3',1.3
union all
select '2019','Q4',1.4
union all
select '2020','Q1',2.1
union all
select '2020','Q2',2.2
union all
select '2020','Q3',2.3
临时表展示为:
需要横向展示不同年份各季度的数量
select Year
,SUM(Case when Quarter = 'Q1' Then Amount Else 0 End) as Q1
,SUM(Case when Quarter = 'Q2' Then Amount Else 0 End) as Q2
,SUM(Case when Quarter = 'Q3' Then Amount Else 0 End) as Q3
,SUM(Case when Quarter = 'Q4' Then Amount Else 0 End) as Q4
from #amount
Group by Year
-- 横向展示的第一列需要:放在select的第一部分,并且需要进行 group by 操作
-- 在写case when 时为了避免null 值带来的影响,这里使用 Else 0 End,即出现null 则返回 0
其结果展示为: