SQL Server转PostgreSQL：UNPIVOT、PIVOT及FORMAT函数替代方案咨询

核心替代逻辑 #

PostgreSQL没有UNPIVOT、PIVOT和FORMAT函数，对应替代方式如下：

• UNPIVOT：通过LATERAL JOIN结合VALUES列表实现列转行
• PIVOT：PostgreSQL 11及以上支持PIVOT语法，低版本需用**条件聚合（CASE + 聚合函数）**实现行转列
• FORMAT：用TO_CHAR函数格式化数值，格式符'FM999,999,999.00'匹配SQL Server的'#,0.00'（FM用于去除数值前的多余空格）
• ISNULL：替换为标准SQL函数COALESCE处理空值，功能与SQL Server的ISNULL一致

兼容所有PostgreSQL版本的脚本 #

（注：原SQL Server脚本存在逻辑矛盾，PIVOT未包含主查询引用的ccode='2'、ccode='3'，转换时已修正该问题，若需求不同可自行调整）

SELECT
    "values",
    TO_CHAR(COALESCE(code_1, 0), 'FM999,999,999.00') AS "Code 1",
    TO_CHAR(COALESCE(code_2, 0), 'FM999,999,999.00') AS "Code 2",
    TO_CHAR(COALESCE(code_3, 0), 'FM999,999,999.00') AS "code 3",
    TO_CHAR(
        COALESCE(code_1, 0) + COALESCE(code_6, 0) + COALESCE(code_G, 0),
        'FM999,999,999.00'
    ) AS "all"
FROM (
    SELECT
        "values",
        MAX(CASE WHEN ccode = '1' THEN value END) AS code_1,
        MAX(CASE WHEN ccode = '2' THEN value END) AS code_2,
        MAX(CASE WHEN ccode = '3' THEN value END) AS code_3,
        MAX(CASE WHEN ccode = '6' THEN value END) AS code_6,
        MAX(CASE WHEN ccode = 'G' THEN value END) AS code_G
    FROM (
        -- 模拟UNPIVOT：将vq、vq1、vq2列转为行数据
        SELECT
            ccode,
            unpiv."values",
            unpiv.value
        FROM abc
        LATERAL (
            VALUES
                ('vq', vq),
                ('vq1', vq1),
                ('vq2', vq2)
        ) AS unpiv("values", value)
    ) AS unpivoted_data
    GROUP BY "values"
) AS pivoted_data;

基于PostgreSQL 11+的简化脚本 #

如果使用PostgreSQL 11及以上版本，可直接使用PIVOT语法：

SELECT
    "values",
    TO_CHAR(COALESCE("1", 0), 'FM999,999,999.00') AS "Code 1",
    TO_CHAR(COALESCE("2", 0), 'FM999,999,999.00') AS "Code 2",
    TO_CHAR(COALESCE("3", 0), 'FM999,999,999.00') AS "code 3",
    TO_CHAR(
        COALESCE("1", 0) + COALESCE("6", 0) + COALESCE("G", 0),
        'FM999,999,999.00'
    ) AS "all"
FROM (
    SELECT
        ccode,
        "values",
        value
    FROM abc
    LATERAL (
        VALUES
            ('vq', vq),
            ('vq1', vq1),
            ('vq2', vq2)
    ) AS unpiv("values", value)
) AS src
PIVOT (
    MAX(value) FOR ccode IN ("1", "2", "3", "6", "G")
) AS piv;

注意事项 #

• PostgreSQL中对大小写敏感或包含特殊字符的标识符（如values、Code 1）需要用双引号包裹
• 若数值范围更大，可调整TO_CHAR的格式符（如增加更多9位数）
• 原SQL脚本的逻辑矛盾已在转换时修正，若实际业务不需要ccode='2'、ccode='3'的列，可删除对应CASE语句或PIVOT中的项

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