PostgreSQL无PIVOT函数实现动态列转行（按产品汇总金额）

方法一：动态生成列的关系表输出 #

实现思路 #

通过PL/pgSQL动态拼接SQL语句，自动抓取所有唯一产品作为列，利用FILTER子句按产品分组汇总月度销售额，适配任意数量的产品。

步骤1：创建动态透视函数 #

CREATE OR REPLACE FUNCTION pivot_ventas()
RETURNS TABLE (month INT) -- 动态生成的产品列会自动追加
AS $$
DECLARE
    product_columns TEXT;
BEGIN
    -- 生成所有产品对应的聚合列语句，用COALESCE将NULL转为0（可选）
    SELECT string_agg(
        format('COALESCE(SUM(amount) FILTER (WHERE product = %L), 0) AS %I', product, product),
        ', '
    ) INTO product_columns
    FROM (SELECT DISTINCT product FROM ventas ORDER BY product) AS unique_products;

    -- 执行动态生成的完整SQL
    RETURN QUERY EXECUTE format(
        'SELECT month, %s FROM ventas GROUP BY month ORDER BY month',
        product_columns
    );
END;
$$ LANGUAGE plpgsql;

步骤2：调用函数获取结果 #

SELECT * FROM pivot_ventas();

输出结果 #

（若需保留NULL值，去掉COALESCE(..., 0)即可）

方法二：JSON聚合输出（适合大量产品场景） #

实现思路 #

先按month和product预汇总销售额，再通过json_object_agg将每个月的产品销售额聚合为JSON对象，无需动态生成列，避免列数膨胀问题，适合产品数量极多的场景。

执行SQL #

SELECT
    month,
    json_object_agg(product, total_amount) AS product_sales
FROM (
    -- 先计算每个月每个产品的总销售额
    SELECT
        month,
        product,
        SUM(amount) AS total_amount
    FROM ventas
    GROUP BY month, product
) AS grouped_sales
GROUP BY month
ORDER BY month;

输出结果 #

两种方法对比 #

• 动态列方法：返回标准关系表结构，直观易读，适合报表导出或直接查询；但产品数量过多时会导致列数膨胀，影响性能和可读性。
• JSON聚合方法：结构紧凑，性能稳定，适合产品数量极大的场景；需应用程序额外解析JSON数据，更适配后端业务逻辑处理。

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