PL/SQL查询结果行转列需求求助（已尝试Pivot未解决）

现有PL/SQL查询结果如下：

需要将其转换为以下格式：

Module ID   |  1     | 2
Module Name |  IGen3 | IGen4
Generation  |  3     | 4
Year        |  2002  | 2003

作为PL/SQL初学者，尝试使用Pivot但未成功实现需求，求解决方案。

解决方案 #

方法一：静态PIVOT（已知模块数量） #

如果模块数量固定，可直接用PIVOT配合字符串拼接实现，示例代码如下：

WITH module_data AS (
    SELECT 1 AS module_id, 'IGen3' AS module_name, 3 AS generation, 2002 AS year FROM DUAL
    UNION ALL
    SELECT 2 AS module_id, 'IGen4' AS module_name, 4 AS generation, 2003 AS year FROM DUAL
)
SELECT 
    'Module ID' AS attribute,
    TO_CHAR(pivot_1) AS "1",
    TO_CHAR(pivot_2) AS "2"
FROM module_data
PIVOT (
    MAX(module_id) FOR module_id IN (1 AS pivot_1, 2 AS pivot_2)
)
UNION ALL
SELECT 
    'Module Name' AS attribute,
    pivot_1,
    pivot_2
FROM module_data
PIVOT (
    MAX(module_name) FOR module_id IN (1 AS pivot_1, 2 AS pivot_2)
)
UNION ALL
SELECT 
    'Generation' AS attribute,
    TO_CHAR(pivot_1) AS "1",
    TO_CHAR(pivot_2) AS "2"
FROM module_data
PIVOT (
    MAX(generation) FOR module_id IN (1 AS pivot_1, 2 AS pivot_2)
)
UNION ALL
SELECT 
    'Year' AS attribute,
    TO_CHAR(pivot_1) AS "1",
    TO_CHAR(pivot_2) AS "2"
FROM module_data
PIVOT (
    MAX(year) FOR module_id IN (1 AS pivot_1, 2 AS pivot_2)
);

方法二：动态SQL（模块数量不固定） #

如果模块数量不确定，建议用动态SQL生成查询语句，自动适配所有模块：

DECLARE
    v_sql VARCHAR2(4000);
    v_module_cols VARCHAR2(1000);
BEGIN
    -- 获取所有模块ID，拼接成PIVOT需要的列格式
    SELECT LISTAGG('' || module_id || ' AS pivot_' || module_id, ', ') WITHIN GROUP (ORDER BY module_id)
    INTO v_module_cols
    FROM (SELECT DISTINCT module_id FROM your_table_name); -- 替换为你的实际表名

    -- 构建动态SQL语句
    v_sql := '
        WITH module_data AS (
            SELECT module_id, module_name, generation, year FROM your_table_name -- 替换为你的实际表名
        )
        SELECT ''Module ID'' AS attribute, ' || 
        LISTAGG('TO_CHAR(pivot_' || module_id || ') AS "' || module_id || '"', ', ') WITHIN GROUP (ORDER BY module_id) || '
        FROM module_data
        PIVOT (
            MAX(module_id) FOR module_id IN (' || v_module_cols || ')
        )
        UNION ALL
        SELECT ''Module Name'' AS attribute, ' || 
        LISTAGG('pivot_' || module_id || ' AS "' || module_id || '"', ', ') WITHIN GROUP (ORDER BY module_id) || '
        FROM module_data
        PIVOT (
            MAX(module_name) FOR module_id IN (' || v_module_cols || ')
        )
        UNION ALL
        SELECT ''Generation'' AS attribute, ' || 
        LISTAGG('TO_CHAR(pivot_' || module_id || ') AS "' || module_id || '"', ', ') WITHIN GROUP (ORDER BY module_id) || '
        FROM module_data
        PIVOT (
            MAX(generation) FOR module_id IN (' || v_module_cols || ')
        )
        UNION ALL
        SELECT ''Year'' AS attribute, ' || 
        LISTAGG('TO_CHAR(pivot_' || module_id || ') AS "' || module_id || '"', ', ') WITHIN GROUP (ORDER BY module_id) || '
        FROM module_data
        PIVOT (
            MAX(year) FOR module_id IN (' || v_module_cols || ')
        )';

    -- 执行动态SQL并输出结果
    EXECUTE IMMEDIATE v_sql;
END;
/

说明 #

• 静态PIVOT适合模块数量固定的场景，写法直接但扩展性差；
• 动态SQL能自动适配任意数量的模块，需替换代码中的your_table_name为实际表名；
• 使用MAX()聚合函数是因为PIVOT必须配合聚合操作，对于每个模块ID来说，每个属性值唯一，MAX()不会改变结果。

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