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

werew1

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