如何将PostgreSQL动态生成的透视表值转置为行

表结构与数据 #

首先是基础的表定义和测试数据：

CREATE TABLE cross_table (brand varchar(10), gender varchar(10), sales int);

INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 30);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 40);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Male', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Nike', 'Male', 20);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Puma', 'Woman', 10);
INSERT INTO cross_table (brand, gender, sales) VALUES ('Adidas', 'Woman', 20);

需求说明 #

当前查询将统计结果聚合为JSON格式，但需要转换为按统计指标（Sum/Count）分行，按gender分列的报表，同时支持brand和gender取值动态变化，无需硬编码。

期望输出示例：

解决方案 #

由于需要动态适配brand和gender的取值，需使用动态SQL结合聚合、行转列（Unpivot）和列转行（Pivot）操作实现：

动态SQL实现 #

DO $$
DECLARE
    gender_columns TEXT;
    final_sql TEXT;
BEGIN
    -- 动态生成所有gender对应的透视列表达式
    SELECT string_agg(DISTINCT format('MAX(CASE WHEN gender = %L THEN stat_value END) AS %I', gender, gender), ', ')
    INTO gender_columns
    FROM cross_table;

    -- 拼接最终查询语句
    final_sql := format('
WITH base_stats AS (
    -- 计算基础统计值，包含品牌-性别、性别总计、全局总计
    SELECT 
        brand,
        gender,
        SUM(sales) AS sum_sales,
        COUNT(sales) AS count_sales,
        GROUPING(brand) AS is_total_brand
    FROM cross_table
    GROUP BY GROUPING SETS ((brand, gender), (gender), ())
),
stats_unpivoted AS (
    -- 将Sum和Count两个指标拆分为行
    SELECT 
        brand,
        stat_type,
        gender,
        stat_value
    FROM base_stats
    CROSS JOIN LATERAL (
        VALUES 
            (''Sum of Sales'', sum_sales),
            (''Count of Sales'', count_sales)
    ) AS stats(stat_type, stat_value)
),
stats_pivoted AS (
    -- 按gender进行透视，计算每个品牌-指标的各性别统计值和总计
    SELECT 
        brand,
        stat_type AS "values",
        %s,
        SUM(stat_value) AS "Total"
    FROM stats_unpivoted
    GROUP BY brand, stat_type
)
-- 处理brand列的显示（同一品牌的第二行留空）并排序
SELECT 
    CASE 
        WHEN row_number() OVER (PARTITION BY brand ORDER BY "values") = 1 THEN brand 
        ELSE '''' 
    END AS "brand",
    "values",
    %s,
    "Total"
FROM stats_pivoted
ORDER BY 
    CASE WHEN brand IS NULL THEN 0 ELSE 1 END, -- 全局总计行排在最前
    brand,
    "values";', gender_columns, gender_columns);

    -- 执行动态生成的SQL
    EXECUTE final_sql;
END $$;

关键步骤说明 #

1. 动态生成gender列：通过string_agg和format函数，自动为所有distinct的gender生成对应的透视列，无需硬编码。
2. 基础统计聚合：使用GROUPING SETS生成三层统计数据：品牌-性别、性别总计、全局总计，覆盖所有需要的统计维度。
3. Unpivot转换：通过LATERAL VALUES将Sum和Count两个统计指标从列转为行，满足报表按指标分行的需求。
4. Pivot转换：通过MAX(CASE...)实现按gender列转行，同时计算Total列。
5. 美化显示：使用row_number()窗口函数处理同一品牌的第二行brand列留空，排序确保全局总计行优先显示。

注意事项 #

• 执行该PL/pgSQL块后，会直接输出期望格式的报表。
• 若新增brand或gender取值，无需修改代码，动态SQL会自动适配。
• 若需要将结果保存为临时表或视图，可在EXECUTE前调整final_sql，添加CREATE TEMP TABLE ... AS或CREATE VIEW ... AS语句。

内容的提问来源于Stack Exchange，提问作者Marius Lian