Oracle 10g WM_CONCAT用法及注意點

標籤：style   blog   color   io   使用   ar   for   strong   sp   

 

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.SBBH10                         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

 

Oracle 10g WM_CONCAT用法及注意點