PostgreSQL实现动态crosstab列：未知月份的行列转置方案

你可以通过PL/pgSQL动态SQL实现自动根据month列的所有取值生成转置列，完全在数据库内完成，无需外部工具。以下是具体实现：

步骤1：创建动态转置函数 #

CREATE OR REPLACE FUNCTION dynamic_crosstab_test()
RETURNS SETOF record AS $$
DECLARE
    month_columns text;
    crosstab_query text;
BEGIN
    -- 获取所有唯一月份值，拼接成动态列定义（给月份加r前缀，避免数字开头的标识符问题）
    SELECT string_agg(DISTINCT format('%I float', 'r' || month), ', ')
    INTO month_columns
    FROM test
    ORDER BY month; -- 按月份排序，保证列顺序为时间递增

    -- 构建完整的crosstab动态查询语句
    crosstab_query := format(
        'SELECT * FROM crosstab(
            ''SELECT category, month, sum FROM test ORDER BY 1,2'',
            ''SELECT DISTINCT month FROM test ORDER BY month''
        ) AS ct(category text, %s)',
        month_columns
    );

    -- 执行动态查询并返回结果
    RETURN QUERY EXECUTE crosstab_query;
END;
$$ LANGUAGE plpgsql;

步骤2：调用函数 #

由于返回的是动态列，调用时需要明确指定列名（或在psql中借助工具辅助查看），示例调用方式：

-- 假设当前test视图的month列包含r202208、r202209两个值
SELECT * FROM dynamic_crosstab_test() AS ct(category text, r202208 float, r202209 float);

如果不想手动指定列名，可先查询列名列表再复制使用：

-- 获取最新的列定义字符串
SELECT string_agg(DISTINCT format('%I float', 'r' || month), ', ') FROM test ORDER BY month;

关键细节说明 #

• string_agg用于拼接所有月份对应的列定义，DISTINCT确保每个月份仅生成一列，ORDER BY month保证列按时间顺序排列。
• crosstab的第二个参数指定了月份的排序规则，避免转置后列顺序混乱。
• 函数每次执行都会重新读取test视图的最新月份数据，无需手动维护列定义。

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