Oracle 10g WM_CONCAT行转列函数用法及注意点

 

WM_CONCAT(colName)为Oracle 10g中行数据转列数据的内置函数，执行效果如下

表T

   A    B
------------
   1    2
   1    3
   1    4
   2    1
   2    3

执行语句 ：

SELECT A, WM_CONCAT(B) B FROM T GROUP BY A

执行结果：

   A    B
---------------
   1    2,3,4
   2    1,3

 

注意：

　　当在包体内使用行转列时，WM_CONCAT(colName)可以使用，但WM_CONCAT(DISTINCT colName)会在包编译时会报错，

　　例如，在某一包的某一存储过程中有如下语句：

 1 UPDATE D_LOWAREA L
 2    SET L.BYQXH = (SELECT /*+index(b)*/
 3                    WM_CONCAT(DISTINCT XH.XHMC)
 4                     FROM OLIVE.OL$_ASSOCIATIONS A,
 5                          PMSBI.F_SB_SBXX_BASIC  B,
 6                          PMSBI.D_XH             XH
 7                    WHERE A.A IN (L.PDBYQ1, L.PDBYQ2, L.PDBYQ3)
 8                      AND A.B = B.SBBH
 9                      AND B.XHDM != 'wz'
10                      AND B.XHDM = XH.XHDM);
11 COMMIT;

 

     编译时会报如下错误：

     PL/SQL：ORA-30482:DISTINCT option not allowed for this function.

 

解决方案：

　　使用动态SQL（字符串中有单引号由两个单引号表示）：

 1    EXECUTE IMMEDIATE 
 2    'UPDATE D_LOWAREA L
 3       SET L.BYQXH = (SELECT /*+index(b)*/
 4                       WMSYS.WM_CONCAT(DISTINCT XH.XHMC)
 5                        FROM OLIVE.OL$_ASSOCIATIONS A,
 6                             PMSBI.F_SB_SBXX_BASIC  B,
 7                             PMSBI.D_XH             XH
 8                       WHERE A.A IN (L.PDBYQ1, L.PDBYQ2, L.PDBYQ3)
 9                         AND A.B = B.SBBH
10                         AND B.XHDM != ''wz''
11                         AND B.XHDM = XH.XHDM)';
12     COMMIT;

 

PS：Oracle 11g以上版本可以用LISTAGG实现行转列，写法如下：

SELECT A, LISTAGG(B, ',') WITHIN GROUP(ORDER BY B) B FROM T GROUP BY A

 

转载于:https://www.cnblogs.com/MickWoo/p/4026726.html