Oracle查询中动态Pivot实现需求（无需手动列与XML）

看起来你想要实现动态Pivot——不用手动写死Characteristic_Id的列名，直接把所有特征值自动转成列，同时避免XML格式的结果。在Oracle里，静态Pivot必须明确指定列名，XML Pivot又只会返回XML类型结果，所以要达成你的需求，得借助动态SQL来自动生成Pivot列。下面是具体的实现方案：

核心思路 #

1. 先从数据源中提取所有唯一的Characteristic_Id，拼接成Pivot需要的IN子句格式（比如'KK-001' AS "KK-001", 'KK-002' AS "KK-002"）。
2. 用这些拼接好的列名动态生成完整的Pivot SQL语句。
3. 执行动态SQL，得到展开列的结果。

具体实现方法 #

方法1：生成可直接执行的静态Pivot SQL #

如果想先看到生成的SQL再手动执行，可以用这个PL/SQL块输出完整的SQL语句：

DECLARE
    v_pivot_cols VARCHAR2(32767);
    v_sql VARCHAR2(32767);
BEGIN
    -- Step 1: 获取所有唯一的Characteristic_Id，拼接成Pivot IN子句格式
    SELECT RTRIM(
               XMLAGG(
                   XMLELEMENT(e, '''' || Characteristic_Id || ''' AS "' || Characteristic_Id || '"', ', ') 
                   ORDER BY Characteristic_Id
               ).EXTRACT('//text()'), 
               ', '
           )
    INTO v_pivot_cols
    FROM (
        SELECT DISTINCT Csv.Characteristic_Id
        FROM Shop_Ord So
        JOIN Config_Spec_Value Csv 
            ON So.Part_No = Csv.Part_No 
            AND So.Configuration_Id = Csv.Configuration_Id
        WHERE So.Configuration_Id != '*' 
          AND So.Need_Date > '01.01.2019' 
          AND So.Part_No LIKE 'XL%'
    );

    -- Step 2: 拼接完整的动态Pivot SQL
    v_sql := '
    WITH Pivot_ AS (
        SELECT So.Order_No, So.Release_No, So.Sequence_No, So.Part_No, 
               Csv.Characteristic_Id, Csv.Characteristic_Value
        FROM Shop_Ord So
        JOIN Config_Spec_Value Csv 
            ON So.Part_No = Csv.Part_No 
            AND So.Configuration_Id = Csv.Configuration_Id
        WHERE So.Configuration_Id != ''*'' 
          AND So.Need_Date > ''01.01.2019'' 
          AND So.Part_No LIKE ''XL%''
    )
    SELECT * 
    FROM Pivot_ 
    PIVOT (
        MAX(Characteristic_Value) 
        FOR(Characteristic_Id) IN (' || v_pivot_cols || ')
    )
    ORDER BY Order_No, Release_No, Sequence_No;';

    -- 输出生成的SQL到DBMS_OUTPUT
    DBMS_OUTPUT.PUT_LINE(v_sql);
END;
/

执行这个块后，复制DBMS_OUTPUT里的SQL语句直接运行，就能得到你想要的展开列结果：

ORDER_NO RELEASE_NO SEQUENCE_NO PART_NO KK-001      KK-002      ...
-------- ---------- ----------- ------- ----------- ----------- ---
E1196    1          1           XL106   KK-001-002  NULL        ...
E1334    1          1           XL107   KK-001-002  00          ...
...

方法2：直接执行动态SQL并返回结果 #

如果想直接通过PL/SQL执行并获取结果，可以用REF CURSOR：

DECLARE
    v_pivot_cols VARCHAR2(32767);
    v_sql VARCHAR2(32767);
    v_result SYS_REFCURSOR;
BEGIN
    -- 同方法1，拼接Pivot列和SQL
    SELECT RTRIM(
               XMLAGG(
                   XMLELEMENT(e, '''' || Characteristic_Id || ''' AS "' || Characteristic_Id || '"', ', ') 
                   ORDER BY Characteristic_Id
               ).EXTRACT('//text()'), 
               ', '
           )
    INTO v_pivot_cols
    FROM (
        SELECT DISTINCT Csv.Characteristic_Id
        FROM Shop_Ord So
        JOIN Config_Spec_Value Csv 
            ON So.Part_No = Csv.Part_No 
            AND So.Configuration_Id = Csv.Configuration_Id
        WHERE So.Configuration_Id != '*' 
          AND So.Need_Date > '01.01.2019' 
          AND So.Part_No LIKE 'XL%'
    );

    v_sql := '
    WITH Pivot_ AS (
        SELECT So.Order_No, So.Release_No, So.Sequence_No, So.Part_No, 
               Csv.Characteristic_Id, Csv.Characteristic_Value
        FROM Shop_Ord So
        JOIN Config_Spec_Value Csv 
            ON So.Part_No = Csv.Part_No 
            AND So.Configuration_Id = Csv.Configuration_Id
        WHERE So.Configuration_Id != ''*'' 
          AND So.Need_Date > ''01.01.2019'' 
          AND So.Part_No LIKE ''XL%''
    )
    SELECT * 
    FROM Pivot_ 
    PIVOT (
        MAX(Characteristic_Value) 
        FOR(Characteristic_Id) IN (' || v_pivot_cols || ')
    )
    ORDER BY Order_No, Release_No, Sequence_No;';

    -- 打开游标返回结果
    OPEN v_result FOR v_sql;
    -- 在PL/SQL Developer或SQL Developer中，执行后可以查看游标结果
END;
/

关键细节说明 #

• 为什么用MAX()聚合函数：因为每个(Order_No, Release_No, Sequence_No, Part_No, Characteristic_Id)组合应该只有一个特征值，MAX()不会改变结果；如果存在多个值，你可以换成LISTAGG()来合并多个值。
• XMLAGG替代LISTAGG：LISTAGG有4000字符的长度限制（Oracle 12cR2+支持扩展，但兼容性不如XMLAGG），所以用XMLAGG来拼接更长的列列表更稳妥。

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