PostgreSQL实现动态行转列（PIVOT效果）的SQL查询方案求助

针对你需要将动态Product_Code作为列名、Product_Name作为对应列值的需求，可以通过PostgreSQL的动态SQL结合字符串拼接实现，以下是具体方案：

核心思路 #

1. 提取所有唯一的Product_Code值，动态生成对应的列映射逻辑
2. 将生成的列逻辑拼接进主查询，执行动态SQL完成行列转换

具体实现 #

方式1：使用DO块执行动态查询 #

适合直接在数据库中执行查看结果：

DO $$
DECLARE
    dynamic_columns TEXT;
BEGIN
    -- 生成每个Product_Code对应的CASE分支，自动处理列名转义和SQL注入
    SELECT string_agg(
        DISTINCT format(
            'MAX(CASE WHEN p.product_code = %L THEN pv.product_name END) AS %I',
            p.product_code,
            p.product_code
        ),
        ', '
    ) INTO dynamic_columns
    FROM product p;

    -- 执行拼接好的动态查询
    EXECUTE format('
        SELECT pv.product_id, %s
        FROM product_values pv
        JOIN product p ON pv.product_id = p.product_id
        GROUP BY pv.product_id
        ORDER BY pv.product_id
    ', dynamic_columns);
END $$;

方式2：创建函数返回动态结果集 #

适合在应用中调用：

CREATE OR REPLACE FUNCTION get_pivoted_products()
RETURNS SETOF record AS $$
DECLARE
    dynamic_columns TEXT;
BEGIN
    SELECT string_agg(
        DISTINCT format(
            'MAX(CASE WHEN p.product_code = %L THEN pv.product_name END) AS %I',
            p.product_code,
            p.product_code
        ),
        ', '
    ) INTO dynamic_columns
    FROM product p;

    RETURN QUERY EXECUTE format('
        SELECT pv.product_id, %s
        FROM product_values pv
        JOIN product p ON pv.product_id = p.product_id
        GROUP BY pv.product_id
        ORDER BY pv.product_id
    ', dynamic_columns);
END $$ LANGUAGE plpgsql;

调用函数时需要指定返回列的结构（需与实际生成的列匹配）：

SELECT * FROM get_pivoted_products() AS t(product_id integer, code_a varchar, code_b varchar);

关键细节说明 #

• format('%L', p.product_code)：将字符串值转义为合法的SQL字符串常量，避免SQL注入风险
• format('%I', p.product_code)：将Product_Code转换为合法的SQL标识符（列名），自动处理包含特殊字符、关键字的情况
• MAX()聚合函数：确保每个product_id只返回一行数据，若同一product_id对应多个相同Product_Code的记录，会取非空的Product_Name；若需合并多值，可替换为STRING_AGG(pv.product_name, ', ')
• DISTINCT：避免重复生成相同Product_Code的列逻辑

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