使用WITH子句在PostgreSQL中实现数据透视转换

需求说明 #

需要将包含Product、Employee、Sales、Sales_inUSD、Sales_inEuro字段的原始表，转换为以Product、Anand、Yash、Currency为字段的目标表，要求必须使用WITH子句，不能创建新表，且适配不支持PIVOT/UNPIVOT语法的PostgreSQL环境。

原始表数据 #

Product | Employee | Sales | Sales_inUSD | Sales_inEuro
A       | Anand    | 10000 | 121.24      | 114.26
A       | Yash     | 12000 | 145.51      | 137.10
B       | Anand    | 13000 | 157.64      | 148.37
B       | Yash     | 15000 | 181.89      | 171.42

目标表数据 #

Product | Anand   | Yash    | Currency
A       | 10000   | 12000   | Rupee
A       | 121.24  | 145.51  | USD
A       | 114.26  | 137.10  | Euro
B       | 13000   | 15000   | Rupee
B       | 157.64  | 181.89  | USD
B       | 148.37  | 171.42  | Euro

实现SQL #

WITH unpivoted_data AS (
    -- 模拟UNPIVOT：拆分不同货币的销售数据为单条记录
    SELECT Product, Employee, Sales AS amount, 'Rupee' AS Currency
    FROM 原始表名
    UNION ALL
    SELECT Product, Employee, Sales_inUSD AS amount, 'USD' AS Currency
    FROM 原始表名
    UNION ALL
    SELECT Product, Employee, Sales_inEuro AS amount, 'Euro' AS Currency
    FROM 原始表名
),
pivoted_data AS (
    -- 模拟PIVOT：按产品和货币分组，提取两人的对应金额
    SELECT 
        Product,
        Currency,
        MAX(CASE WHEN Employee = 'Anand' THEN amount END) AS Anand,
        MAX(CASE WHEN Employee = 'Yash' THEN amount END) AS Yash
    FROM unpivoted_data
    GROUP BY Product, Currency
)
-- 输出目标格式结果
SELECT Product, Anand, Yash, Currency
FROM pivoted_data
ORDER BY Product, Currency;

逻辑说明 #

1. unpivoted_data 子句：用UNION ALL把原始表中三种货币的销售字段拆分成单条记录，每条记录对应一种货币，替代UNPIVOT功能。
2. pivoted_data 子句：以产品和货币为分组维度，通过CASE WHEN配合MAX聚合，把Anand和Yash的金额提取为独立字段，替代PIVOT功能。
3. 最后调整字段顺序并排序，得到目标表结构。

注意：请将代码中的原始表名替换为你实际使用的表名称。

内容的提问来源于stack exchange，提问作者Panda