2014-06-25

[SQL] 將多行撈取結果轉為逗點分隔字串 (Transform SQL Rows into Comma-Separated String in Oracle)

阿布洛格 Oracle在撈報表資料時,有時會遇到一對多的資料,比方說一張訂單裡有多個商品,更往往會被要求這些多筆的資料,放進一個比方說購買商品的欄位裡作呈現。但其實,這對以 RowData 為概念的資料庫操作而言,並不是那麼理所當然。

速記一下這一篇…免得每次遇到這種 Case 總要搞半天…

就舉訂單編號與訂單商品間一對多的資料關係作為例子:
原始資料 (Base Data):
  ORDER_NO PROD_NAME
---------- ----------
   ODR0001 PROD_A
   ODR0001 PROD_B
   ODR0001 PROD_C
   ODR0002 PROD_D
   ODR0002 PROD_E
   ODR0003 PROD_F
   ODR0003 PROD_G
   ODR0003 PROD_H
   ODR0003 PROD_I

期望輸出 (Desired Output):
  ORDER_NO PROD_LIST
---------- --------------------------------------------------
   ODR0001 PROD_A,PROD_B,PROD_C
   ODR0002 PROD_D,PROD_E
   ODR0003 PROD_F,PROD_G,PROD_H,PROD_I

在 Oracle 11g Release 2 之後,有個方便的內建 function LISTAGG 可以使用:
SELECT order_no, LISTAGG(prod_name, ',') WITHIN GROUP (ORDER BY prod_name) AS prod_list
  FROM orders
 GROUP BY order_no;


== Result ===================================================
  ORDER_NO PROD_LIST
---------- --------------------------------------------------
   ODR0001 PROD_A,PROD_B,PROD_C
   ODR0002 PROD_D,PROD_E
   ODR0003 PROD_F,PROD_G,PROD_H,PROD_I

3 rows selected.
這種解法十分簡潔,從 SQL 語法上也還不難理解,但世事往往不能盡如人意,如果像阿布一樣,遇上的資料庫環境是更早的 Oracle 版本,那這條捷徑是行不通的。

原則上,阿布還是希望在不 CREATE 額外的 PROCEDURE / FUNCTION,在不借助額外 COLLECT / OBJECT 的前提下,單純以 SQL SELECT 來達成。因為客觀來講,往往在使用 Oracle 這種規模的資料庫環境,有時要下個 CREATE 是要跑過層層的簽核程序的,如果是尋求 Long-term Solution 是無可厚非,但是往往面臨的是臨時的需求或是一次性使用,殺雞焉用牛刀,也緩不濟急。

williamrobertson.net 搜尋到了讓阿布眼睛一亮的 solution。在 Oracle 9i 之後,可以使用 ROW_NUMBER()SYS_CONNECT_BY_PATH 函數來實作相同的結果:
SELECT order_no,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(prod_name,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS prod_list
  FROM (SELECT order_no,
               prod_name,
               ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY prod_name) AS curr,
               ROW_NUMBER() OVER (PARTITION BY order_no ORDER BY prod_name) -1 AS prev
          FROM orders)
GROUP BY order_no
CONNECT BY prev = PRIOR curr AND order_no = PRIOR order_no
START WITH curr = 1;


== Result ===================================================
  ORDER_NO PROD_LIST
---------- --------------------------------------------------
   ODR0001 PROD_A,PROD_B,PROD_C
   ODR0002 PROD_D,PROD_E
   ODR0003 PROD_F,PROD_G,PROD_H,PROD_I

3 rows selected.
節錄 williamrobertson.net 的說明:
It's possible to use the analytic ROW_NUMBER() function to simulate a hierarchy in the ordered data, and then in an outer query use SYS_CONNECT_BY_PATH (new in 9i) to show that "hierarchy" as one line, and take the last value in each group using the handy KEEP (DENSE_RANK LAST) construction also added in 9i.
概念上,就是用 ROW_NUMBER() 去做出階層式的有序數據,然後用 SYS_CONNECT_BY_PATH 把階層式的有序數據連結,然後用 KEEP (DENSE_RANK LAST) 取回各群組中最後的值作為結果,而 function SYS_CONNECT_BY_PATHKEEP (DENSE_RANK LAST) 架構都是 Oracle 9i 新增的。老實講,阿布功力不夠看得很模糊…雖然這或許不是個有效率的,也不是個優雅的 Query,但是這個 solution 無需使用 PL/SQL 或額外的附加類型定義,完美!



參考資料︰

再來,快速帶過一個三個 Table 關連的範例。
情境︰有個 Table employee 放了員工的基本資料,一個 Table role 放著角色(權限群組)的資料,再來一個 Table employee_role_mapping 對應出各員工的角色,而一個員工可能被賦予多個角色,在撈帳號清單時,需要把這個員工的角色列在同一個欄位中。

Oracle 9i 版:
SELECT employee_no,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(role_name,','))
       KEEP (DENSE_RANK LAST ORDER BY curr),',') AS role_list
  FROM (SELECT p.employee_no,
               r.role_name,
               ROW_NUMBER() OVER (PARTITION BY p.employee_no ORDER BY r.role_name) AS curr,
               ROW_NUMBER() OVER (PARTITION BY p.employee_no ORDER BY r.role_name) -1 AS prev
          FROM employee p, employee_role_mapping m, role r
         WHERE p.employee_no= m.employee_no
           AND m.role_id = r.role_id)
 GROUP BY employee_no
 CONNECT BY prev = PRIOR curr AND employee_no = PRIOR employee_no
 START WITH curr = 1;

Oracle 11g Release 2 版:
SELECT p.employee_no,
       LISTAGG(r.role_name, ',') WITHIN GROUP (ORDER BY r.role_name) AS role_list
  FROM employee p, employee_role_mapping m, role r
 WHERE p.employee_no = m.employee_no
   AND m.role_id = r.role_id
 GROUP BY employee_no;

順帶一提確認 Oracle 版本的 SQL:
SELECT * FROM V$VERSION;



0 回應 :

張貼留言

讓阿布知道你對這篇文章的想法吧!

 
本站係採用創用 CC 姓名標示-非商業性-禁止改作 2.5 台灣 授權條款授權