多聚合列数据透视表实现问题求助

看起来你在处理多聚合列的透视操作时踩了语法坑，这确实是PIVOT功能里常见的问题——不同数据库对多聚合列的支持语法差异很大，咱们一步步来修正：

先排查原代码的核心问题 #

你的原始SQL有两个明显的问题：

• 子查询d里的GROUP BY a.COL3和SELECT的col1, per1, per2不匹配（除非你的数据库允许非聚合列不在GROUP BY里，但这不符合标准SQL，还容易导致数据错误）
• PIVOT子句里直接写max(d.per1), max(d.per2)的语法不合法，大部分数据库的PIVOT不支持这种直接罗列多个聚合函数的写法

针对不同数据库的解决方案 #

方案1：适用于Oracle（支持多聚合列的PIVOT语法） #

Oracle允许在PIVOT中同时指定多个聚合函数，你需要调整语法，给每个聚合结果加后缀区分，同时修正子查询的GROUP BY逻辑：

SELECT *
FROM (
    -- 先在子查询里完成基础聚合，确保GROUP BY和SELECT列匹配
    SELECT 
        a.COL3,
        b.col1,
        MAX(b.per1) AS per1,
        MAX(b.per2) AS per2
    FROM table_a a
    LEFT JOIN table_b b ON a.ID = b.ID
    WHERE a.END_DTTM IS NULL
    GROUP BY a.COL3, b.col1
) d
PIVOT (
    MAX(per1) AS per1, MAX(per2) AS per2  -- 给每个聚合结果加后缀，避免列名冲突
    FOR col1 IN (
        'A' AS col_A,  -- 字符串类型的col1值要加单引号，同时给透视列取清晰别名
        'B' AS col_B,
        'C' AS col_C
    )
) piv;

方案2：适用于SQL Server/PostgreSQL（不支持多聚合列直接PIVOT） #

这类数据库的PIVOT只支持单个聚合函数，咱们可以用先UNPIVOT再PIVOT的思路，把per1和per2转成行，再统一透视：

-- SQL Server版本示例
SELECT *
FROM (
    SELECT 
        a.COL3,
        CONCAT(col1, '_', col) AS pivot_col,  -- 把col1和聚合列名拼接成新的透视标识
        value
    FROM (
        SELECT 
            a.COL3,
            b.col1,
            MAX(b.per1) AS per1,
            MAX(b.per2) AS per2
        FROM table_a a
        LEFT JOIN table_b b ON a.ID = b.ID
        WHERE a.END_DTTM IS NULL
        GROUP BY a.COL3, b.col1
    ) src
    UNPIVOT (
        value FOR col IN (per1, per2)
    ) unpvt
) d
PIVOT (
    MAX(value)
    FOR pivot_col IN (
        A_per1, A_per2,
        B_per1, B_per2,
        C_per1, C_per2
    )
) piv;

方案3：通用CASE WHEN写法（兼容所有数据库） #

如果不想依赖数据库特定的PIVOT语法，用CASE WHEN手动实现透视是最稳妥的方式，逻辑清晰且全兼容：

SELECT
    a.COL3,
    MAX(CASE WHEN b.col1 = 'A' THEN b.per1 END) AS A_per1,
    MAX(CASE WHEN b.col1 = 'A' THEN b.per2 END) AS A_per2,
    MAX(CASE WHEN b.col1 = 'B' THEN b.per1 END) AS B_per1,
    MAX(CASE WHEN b.col1 = 'B' THEN b.per2 END) AS B_per2,
    MAX(CASE WHEN b.col1 = 'C' THEN b.per1 END) AS C_per1,
    MAX(CASE WHEN b.col1 = 'C' THEN b.per2 END) AS C_per2
FROM table_a a
LEFT JOIN table_b b ON a.ID = b.ID
WHERE a.END_DTTM IS NULL
GROUP BY a.COL3;

额外提示 #

• 如果col1是数字类型，就不用给值加单引号；如果是字符串必须加
• 子查询的GROUP BY必须包含所有非聚合列，否则会出现语法错误或数据失真
• 如果子查询已经保证每行唯一，MAX可以换成SUM或者直接保留，但用MAX/SUM能避免重复行导致的结果异常

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