如何用动态PL/SQL将多列逆透视转为多行（Oracle）

问题情况 #

静态SQL里用UNPIVOT将A、B、C、D多列转成多行完全正常，示例代码如下：

SELECT A, B, C, D from table UNPIVOT (
(A,B,C,D) FOR values in (
                        (A_1, B_1, C_1, D_1)as 'cat1',
                        (A_2, B_2, C_2, D_2)as 'cat2',
                        (A_3, B_3, C_3, D_3)as 'cat3',
                        (A_4, B_4, C_4, D_4)as 'cat4'
))p 

但改成动态PL/SQL后就触发“缺少右括号”错误，只有单列的动态UNPIVOT能正常运行。报错的动态代码如下：

Declare 
col_list_A  CLOB;
col_list_B  CLOB;
col_list_C  CLOB;
col_list_D  CLOB;
Viewsql    CLOB;

Begin


SELECT  listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
INTO col_list_A FROM all_tab_columns
WHERE table_name = 'table_xxx' and column_name like 'A_%' ;   

SELECT  listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
INTO col_list_B FROM all_tab_columns
WHERE table_name = 'table_xxx' and column_name like 'B_%' ;    

SELECT  listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
INTO col_list_C FROM all_tab_columns
WHERE table_name = 'table_xxx' and column_name like 'C_%' ;    

SELECT  listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
INTO col_list_D FROM all_tab_columns
WHERE table_name = 'table_xxx' and column_name like 'D_%' ;    

 
Viewsql := 'SELECT A,B,C,D,VAL_NAME FROM table_xxx UNPIVOT (
(A,B,C,D) FOR VAL_NAME IN ('||col_list_A ||','||col_list_B ||','||col_list_C ||','||col_list_D ||')' ; 

execute immediate 'CREATE or REPLACE VIEW table_xxx_view AS ' || viewsql;

End;
/
select * from table_xxx_view

单列能成功运行的动态代码参考：

Declare 
col_list_A  CLOB;
Viewsql    CLOB;

Begin


SELECT  listagg(column_name,',') WITHIN GROUP(ORDER BY column_name)
INTO col_list_A FROM all_tab_columns
WHERE table_name = 'table_xxx' and column_name like 'A_%' ; 

Viewsql := 'SELECT A,VAL_NAME FROM table_xxx UNPIVOT (
A FOR VAL_NAME IN ('||col_list_A ||')' ; 

execute immediate 'CREATE or REPLACE VIEW table_xxx_view AS ' || viewsql;

End;
/
select * from table_xxx_view

问题出在哪？ #

1. 列拼接逻辑完全错误：多列UNPIVOT要求IN子句里是一组组的列组合，比如(A_1,B_1,C_1,D_1) as 'cat1'，但当前代码是把A、B、C、D各自的列列表直接拼接，生成的SQL会变成(A_1,A_2,A_3,A_4,B_1,B_2,...)，完全不符合多列UNPIVOT的语法要求。
2. 缺少闭合括号：静态SQL里UNPIVOT有两层闭合括号（))p），但动态代码只写了一层左括号，最终生成的SQL缺少右括号，这就是报“缺少右括号”的直接原因。

正确的修改方法 #

第一步：生成正确的列组合列表 #

需要把同编号的A_、B_、C_*、D_*配对，生成(A_n,B_n,C_n,D_n) as 'catn'格式的字符串。可以通过列名中的数字后缀来分组：

SELECT LISTAGG('(' || A_col || ',' || B_col || ',' || C_col || ',' || D_col || ') as ''cat' || num || '''', ',') WITHIN GROUP (ORDER BY num)
INTO unpivot_items
FROM (
    SELECT 
        column_name AS A_col,
        REPLACE(column_name, 'A_', '') AS num
    FROM all_tab_columns
    WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'A_%'
) A
JOIN (
    SELECT 
        column_name AS B_col,
        REPLACE(column_name, 'B_', '') AS num
    FROM all_tab_columns
    WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'B_%'
) B ON A.num = B.num
JOIN (
    SELECT 
        column_name AS C_col,
        REPLACE(column_name, 'C_', '') AS num
    FROM all_tab_columns
    WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'C_%'
) C ON A.num = C.num
JOIN (
    SELECT 
        column_name AS D_col,
        REPLACE(column_name, 'D_', '') AS num
    FROM all_tab_columns
    WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'D_%'
) D ON A.num = D.num;

第二步：修正动态SQL的拼接逻辑 #

确保生成的SQL语法完整，包含所有必要的闭合括号：

Declare 
unpivot_items  CLOB;
Viewsql        CLOB;

Begin
    -- 生成符合要求的UNPIVOT项列表
    SELECT LISTAGG('(' || A_col || ',' || B_col || ',' || C_col || ',' || D_col || ') as ''cat' || num || '''', ',') WITHIN GROUP (ORDER BY num)
    INTO unpivot_items
    FROM (
        SELECT 
            column_name AS A_col,
            REPLACE(column_name, 'A_', '') AS num
        FROM all_tab_columns
        WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'A_%'
    ) A
    JOIN (
        SELECT 
            column_name AS B_col,
            REPLACE(column_name, 'B_', '') AS num
        FROM all_tab_columns
        WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'B_%'
    ) B ON A.num = B.num
    JOIN (
        SELECT 
            column_name AS C_col,
            REPLACE(column_name, 'C_', '') AS num
        FROM all_tab_columns
        WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'C_%'
    ) C ON A.num = C.num
    JOIN (
        SELECT 
            column_name AS D_col,
            REPLACE(column_name, 'D_', '') AS num
        FROM all_tab_columns
        WHERE table_name = 'TABLE_XXX' AND column_name LIKE 'D_%'
    ) D ON A.num = D.num;

    -- 拼接完整的动态SQL
    Viewsql := 'SELECT A,B,C,D,VAL_NAME FROM table_xxx UNPIVOT (
    (A,B,C,D) FOR VAL_NAME IN (' || unpivot_items || ')
)) p'; 

    -- 可选：先打印SQL检查语法，提前发现错误
    -- DBMS_OUTPUT.PUT_LINE(Viewsql);

    execute immediate 'CREATE or REPLACE VIEW table_xxx_view AS ' || viewsql;

End;
/
select * from table_xxx_view

实用小技巧 #

在执行execute immediate之前，用DBMS_OUTPUT.PUT_LINE(Viewsql);把生成的SQL打印出来检查，能提前发现拼接错误，避免反复执行报错。

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