已知如下表:
要得到如下的结果
方法: case when ,collect_list
--建表插入数据
CREATE OR REPLACE TEMPORARY VIEW t1(year, month, amount) AS
VALUES (1991, 1, 1.1),
(1991, 2, 1.2),
(1991, 3, 1.3),
(1991, 4, 1.4),
(1992, 1, 2.1),
(1992, 2, 2.2),
(1992, 3, 2.3),
(1992, 4, 2.4);
--方式一
SELECT year,
MAX(CASE WHEN month = 1 THEN amount END) m1,
MAX(CASE WHEN month = 2 THEN amount END) m2,
MAX(CASE WHEN month = 3 THEN amount END) m3,
MAX(CASE WHEN month = 4 THEN amount END) m4
FROM t1
GROUP BY year;
--方式二
SELECT year,
collect_list(amount)[0] m1,
collect_list(amount)[1] m2,
collect_list(amount)[2] m3,
collect_list(amount)[3] m4
FROM t2
GROUP BY year;
如果使用逗号的话,用concat_ws去拼接那一列就可以.
反过来,列转行用explode
select year,month,amount
from t2
lateral view explode(amount) amount as amount;