【PostgreSQL行转列】

最新推荐文章于 2024-07-19 17:56:37 发布

展翅/mg飞翔

最新推荐文章于 2024-07-19 17:56:37 发布

阅读量333

点赞数

文章标签： postgresql

本文链接：https://blog.csdn.net/dubing168/article/details/130974380

版权

文章介绍了如何在PostgreSQL数据库中实现行转列的操作，分别使用了`crosstab`函数，需要导入`tablefunc`模块，以及利用`GROUPBY`结合`SUM`和`CASEWHEN`的方式。示例展示了如何将销售数据按年份和季度转换为pivot表形式。

摘要由CSDN通过智能技术生成

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;