Oracle动态PIVOT实现咨询：行转列需求及尝试方案

先明确下你的需求场景：
你有一份销售数据，结构如下：

想要把它转成以TYPE为行，各个RANGE值为列的格式，也就是：

你试过直接在PIVOT的IN子句里用子查询，也试过PIVOT XML，但都没达到预期——这很正常，因为Oracle原生的PIVOT语法不支持动态的列名输入，得用动态SQL来解决这个问题。

具体实现方案 #

方法一：用PL/SQL存储过程自动生成并执行动态SQL #

这个方法适合需要反复执行的场景，一次编写后可以直接调用：

CREATE OR REPLACE PROCEDURE GET_DYNAMIC_PIVOT_SALES
IS
    v_sql          VARCHAR2(4000);
    pivot_columns  VARCHAR2(2000);
BEGIN
    -- 第一步：把所有唯一的RANGE值拼接成PIVOT需要的格式
    SELECT LISTAGG('''' || range || ''' AS "' || range || '"', ', ')
           WITHIN GROUP (ORDER BY range DESC)
    INTO pivot_columns
    FROM (SELECT DISTINCT range FROM YOUR_TABLE); -- 替换成你的实际表名

    -- 第二步：拼接完整的动态SQL语句
    v_sql := '
        SELECT *
        FROM (
            SELECT type, range, sales
            FROM YOUR_TABLE -- 同样替换成你的表名
        )
        PIVOT (
            SUM(sales)
            FOR range IN (' || pivot_columns || ')
        )
        ORDER BY type';

    -- 第三步：执行动态SQL并返回结果（适合在SQL*Plus/PL/SQL Developer中运行）
    DECLARE
        v_result_cursor SYS_REFCURSOR;
    BEGIN
        OPEN v_result_cursor FOR v_sql;
        DBMS_SQL.RETURN_RESULT(v_result_cursor);
    END;
END;
/

调用这个存储过程：EXEC GET_DYNAMIC_PIVOT_SALES;，就能直接得到你想要的动态列结果。

方法二：手动生成动态SQL（临时查询场景） #

如果只是临时查一次，不用写存储过程，分两步走就行：

1. 先执行下面的语句，获取PIVOT需要的列名拼接字符串：

SELECT LISTAGG('''' || range || ''' AS "' || range || '"', ', ')
       WITHIN GROUP (ORDER BY range DESC) AS pivot_clause
FROM (SELECT DISTINCT range FROM YOUR_TABLE);

2. 把查询返回的pivot_clause内容复制到PIVOT的IN子句里，组成完整SQL：

SELECT *
FROM (
    SELECT type, range, sales
    FROM YOUR_TABLE
)
PIVOT (
    SUM(sales)
    FOR range IN ('20180301-20180101' AS "20180301-20180101", '20171201-20171031' AS "20171201-20171031", '20170930-20170731' AS "20170930-20170731")
)
ORDER BY type;

执行这个SQL就能得到目标结果，后续如果RANGE有新增值，重复第一步重新生成列名即可。

关于PIVOT XML的补充说明 #

你之前试过的PIVOT XML确实能处理动态列，但它返回的是XML格式的结果，需要额外解析才能转成表格形式，步骤比较繁琐。如果只是需要普通的表格输出，还是上面的动态SQL方案更实用。如果一定要用PIVOT XML，解析的示例如下：

SELECT type,
       x.*
FROM (
    SELECT *
    FROM (
        SELECT type, range, sales
        FROM YOUR_TABLE
    )
    PIVOT XML (
        SUM(sales)
        FOR range IN (SELECT DISTINCT range FROM YOUR_TABLE)
    )
) t,
XMLTABLE('/PivotSet/item'
         PASSING t.range_xml
         COLUMNS range_name VARCHAR2(20) PATH '@column',
                 sales_val NUMBER PATH 'value') x;

不过这个结果和你想要的列格式还是不一样，需要再做一次聚合转列，所以不太推荐。

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