SQL每日一题11：case when行转列

题目

怎么把下面的表（T20191112）

查成这样1个结果

参考答案

create table T20191112
(
Lyear varchar(10),
Lmonth varchar(10),
Amount decimal(18,2)
)

insert into T20191112 
values ('2017','1',1.1);
insert into T20191112 
values ('2017','2',1.2);
insert into T20191112 
values ('2017','3',1.3);
insert into T20191112 
values ('2017','4',1.4);
insert into T20191112 
values ('2018','1',2.1);
insert into T20191112 
values ('2018','2',2.2);
insert into T20191112 
values ('2018','3',2.3);
insert into T20191112 
values ('2018','4',2.4);

--Mysql 8.0和SQL Server
select Lyear,
min(case when Lmonth='1' then Amount 
else null end) as m1,
min(case when Lmonth='2' then Amount 
else null end) as m2,
min(case when Lmonth='3' then Amount 
else null end) as m3,
min(case when Lmonth='4' then Amount 
else null end) as m4
from T20191112
group by Lyear;

解析： 这是经典的行转列题型，通过使用case when取出我们需要的列，再通过聚合函数去掉每一列中的null值，来达到题目的要求。