【PostgreSQL行转列】

PostgreSQL行转列

创建表

CREATE TABLE sales (
year INTEGER,
quarter INTEGER,
sales_amount NUMERIC

插入测试数据

INSERT INTO sales VALUES
(2018, 1, 100),
(2018, 2, 200),
(2018, 3, 300),
(2018, 4, 400),
(2019, 1, 500),
(2019, 2, 600),
(2019, 3, 700),
(2019, 4, 800);

查询

select * from sales;

1、使用 crosstab 函数将行转列，需要使用一个额外的模块 “tablefunc”

SELECT * FROM crosstab(
‘SELECT year, quarter, sales_amount
FROM sales
ORDER BY 1, 2’,
‘SELECT quarter FROM generate_series(1,4) AS quarter’
) AS sales_pivot(year INTEGER, q1 NUMERIC, q2 NUMERIC, q3 NUMERIC, q4 NUMERIC);

2、使用 GROUP BY + SUM + CASE WHEN 也可以实现将行转列

SELECT
year,
SUM(CASE WHEN quarter = 1 THEN sales_amount ELSE 0 END) AS q1,
SUM(CASE WHEN quarter = 2 THEN sales_amount ELSE 0 END) AS q2,
SUM(CASE WHEN quarter = 3 THEN sales_amount ELSE 0 END) AS q3,
SUM(CASE WHEN quarter = 4 THEN sales_amount ELSE 0 END) AS q4
FROM sales
GROUP BY year
ORDER BY year;