Oracle 19c中如何基于多列动态构建Pivot透视表

核心思路 #

由于measure_name会持续新增，静态PIVOT无法适配动态列需求，必须通过动态SQL结合PIVOT功能，自动识别所有存在的measure_name，并生成对应的[measure_name]_value和[measure_name]_type列。

实现步骤与代码示例 #

1. 动态生成并执行PIVOT SQL #

通过PL/SQL匿名块自动生成适配所有measure_name的SQL，并执行输出结果：

DECLARE
    v_sql          VARCHAR2(32767);
    v_pivot_cols   VARCHAR2(32767);
BEGIN
    -- 提取所有唯一measure_name，拼接成PIVOT需要的列定义
    SELECT LISTAGG(
             '''' || measure_name || ''' AS ' || measure_name || '_value, ' ||
             '''' || measure_name || ''' AS ' || measure_name || '_type',
             ', '
           ) WITHIN GROUP (ORDER BY measure_name)
    INTO v_pivot_cols
    FROM (SELECT DISTINCT measure_name FROM your_table_name); -- 替换为实际表名

    -- 拼接完整动态SQL
    v_sql := '
        SELECT *
        FROM (
            -- 将原始数据拆分为统一的键值对格式，为PIVOT做准备
            SELECT 
                item_no,
                item_type,
                measure_name || ''_value'' AS col_name,
                measure_value AS col_value
            FROM your_table_name
            UNION ALL
            SELECT 
                item_no,
                item_type,
                measure_name || ''_type'' AS col_name,
                measure_type AS col_value
            FROM your_table_name
        )
        PIVOT (
            MAX(col_value) -- 每个item_no+item_type+col_name唯一，MAX/MIN均可
            FOR col_name IN (' || v_pivot_cols || ')
        )
        ORDER BY item_no';

    -- 执行动态SQL（直接输出结果）
    EXECUTE IMMEDIATE v_sql;

    -- 可选：打印生成的SQL语句，用于调试或手动执行
    DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/

2. 代码关键部分解释 #

• 动态列拼接：通过SELECT DISTINCT measure_name获取所有已存在的度量名称，再用LISTAGG函数将其拼接为PIVOT子句所需的列列表。
• 数据预处理：用UNION ALL将原始表的measure_value和measure_type转换为[measure_name]_value、[measure_name]_type的键值对结构，统一格式后再执行行转列。
• 聚合函数选择：使用MAX(col_value)是因为每个item_no+item_type+col_name组合仅对应一条数据，聚合函数仅用于实现行转列的逻辑，不影响结果取值。

3. 输出结果示例 #

针对你提供的测试数据，执行后会得到如下宽表：

注意事项 #

• 需将代码中的your_table_name替换为实际表名。
• 若measure_name包含特殊字符（如空格、数字开头），需在拼接列名时用双引号包裹，例如修改为'"' || measure_name || '_value"'。
• 若measure_name数量极多，动态SQL长度可能超出VARCHAR2(32767)限制，此时需将v_sql和v_pivot_cols改为CLOB类型。

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