DB2 行列转置之行转列

千家信息网最后更新 2025年02月01日DB2 行列转置之行转列构造表和数据
CREATE TABLE Sales(Year INTEGER,Quarter INTEGER,Results INTEGER);
insert into sales values(2005,4,27);
insert into sales values(2005,3,12);
insert into sales values(2005,2,40);
insert into sales values(2005,1,18);
insert into sales values(2004,4,10);
insert into sales values(2004,3,15);
insert into sales values(2004,2,30);
insert into sales values(2004,1,20);
select * from sales order by year,quarter

YEAR QUARTER RESULTS
---- ------- -------
2004 1 20
2004 2 30
2004 3 15
2004 4 10
2005 1 18
2005 2 40
2005 3 12
2005 4 27

如果想转换成下面的样子
YEAR Q1 Q2 Q3 Q4
---- -- -- -- --
2004 20 30 15 10
2005 18 40 12 27

转换SQL
SELECT Year
,MAX(CASE
WHEN Quarter = 1
THEN Results
END) AS Q1
,MAX(CASE
WHEN Quarter = 2
THEN Results
END) AS Q2
,MAX(CASE
WHEN Quarter = 3
THEN Results
END) AS Q3
,MAX(CASE
WHEN Quarter = 4
THEN Results
END) AS Q4
FROM Sales
GROUP BY YEAR