一个典型的行转列sql题

题目如图片所示

图A 图B

图A到图B行变少，列变多，所以是行转列
详细sql看下面:

DROP TABLE xyz;
--创建表
create table xyz(
x string,
y string,
z string);
--插入测试数据
insert into xyz values ('A','2019','zb01');
insert into xyz values ('A','2019','zb02');
insert into xyz values ('A','2018','zb01');
insert into xyz values ('A','2018','zb02');
insert into xyz values ('A','2017','zb01');
insert into xyz values ('A','2017','zb02');
insert into xyz values ('B','2019','zb01');
insert into xyz values ('B','2019','zb02');
insert into xyz values ('B','2019','zb03');
insert into xyz values ('B','2018','zb01');
insert into xyz values ('B','2018','zb02');
insert into xyz values ('B','2018','zb03');
insert into xyz values ('B','2017','zb01');
insert into xyz values ('B','2017','zb02');
insert into xyz values ('B','2017','zb03');

查看表数据：

select * from xyz;

行转列的sql：

select x ,y, 
max(case when z='zb01' then z else '' end) as a ,
max(case when z='zb02' then z else '' end) as b ,
max(case when z='zb03' then z else '' end) as c 
from xyz group by x,y;

结果如下：