时间戳

首页 > 解决方案 > Postgres 根据列值将行转置为列

sql - Postgres 根据列值将行转置为列

问题描述

我statement在 Postgres 中有下表：

stock |  year | statement    | amount           

ACME  | 2003  | Q1 Earnings  | 100
ACME  | 2003  | Q2 Earnings  | 200
ACME  | 2004  | Q2 Earnings  | 300

如何制作一张新表，将一年的所有 4 个季度放在一行中？缺失语句的空值。

stock | year | Q1 Earnings | Q2 Earnings | Q3 Earnings | Q4 Earnings 

ACME  | 2003 | 100         | 200         | Null        | Null
ACME  | 2004 | NULL        | 300         | Null        | Null

找到了这个答案：Postgres - Transpose Rows to Columns但它没有显示如何根据另一个值创建和填充新列或处理空值。

标签： sqlpostgresqlswitch-statementtransposecrosstab

解决方案

假设您每年要显示固定的 4 个季度，请使用旋转逻辑：

SELECT
    stock,
    year,
    MAX(amount) FILTER (WHERE statement = 'Q1 Earnings') AS "Q1 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q2 Earnings') AS "Q2 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q3 Earnings') AS "Q3 Earnings",
    MAX(amount) FILTER (WHERE statement = 'Q4 Earnings') AS "Q4 Earnings"
FROM statement
GROUP BY
    stock,
    year;

推荐阅读