Oracle SQL SYS_CONNECT_BY_PATH 合併多行字符串 , Oracle SQL 多筆資料合併為一筆 , Oracle 合併字串, start with connect by prior 樹結構遞歸查詢

資料庫中取得資料時，有時候會需要對資料做一些加工
SYS_CONNECT_BY_PATH 可以將多筆資料轉換成單一一筆資料的方法

在 TB_COMPANY_CATEGORY 中，一間公司可以有多筆資料
我將多片的 CATEGORY_ID 併成一批 COMPANY_ID

但因某組需求，需要用另外一種方式來呈現這樣的關係，像底下這樣的格式

同產業3筆資料轉成一筆資料產出，方法有很多，先研究了個比較特別的方法
需要搭配 Oracle 的 Hierarchy Queries 以及 Oracle 的 Function SYS_CONNECT_BY_PATH()

首先要把上述的3筆資料建立出階層關係，如底下的 SQL
根據 category_id 的順序將同一個 company_id 的 category_id 做出排序並且給予編號 rn。

SELECT company_id , category_id,
ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY category_id) AS rn
FROM tb_company_category
ORDER BY company_id

有了 rn 這個編號以後，就可以分辨出哪筆資料是開始
以及下一筆資料與目前這筆資料間的關係，靠這兩點就能建立 Hierarchy 的關聯

SELECT CPNT.company_id, CPNT.category_id, rn, level
FROM
(SELECT company_id, category_id,
ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY category_id) AS rn
FROM tb_company_category
ORDER BY company_id ) CPNT
START WITH CPNT.rn = 1
CONNECT BY CPNT.company_id = PRIOR CPNT.company_id
AND CPNT.rn – 1 = PRIOR CPNT.rn

上面的這串 SQL 根據 inline Query 中的 rn 欄位建立起 Hierarchy 的資料關聯
注意這裡的 rn 與 level 雖然資料是一樣的，但是前者是資料的順序
後者是 Hierarchy 的階層，意義有所不同，Query 出來的資料大概會長成底下這樣

SELECT CPNT.company_id, CPNT.category_id, SYS_CONNECT_BY_PATH(CPNT.category_id, ‘,’) AS category_id_list
FROM (SELECT company_id, category_id,
ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY category_id) AS rn
FROM tb_company_category
ORDER BY company_id ) CPNT
START WITH CPNT.rn = 1
CONNECT BY CPNT.company_id = PRIOR CPNT.company_id
AND CPNT.rn – 1 = PRIOR CPNT.rn

這樣的 SQL 產生出來的資料只是單純的把該筆資料
之前的階層都以 “," 為 Delimeter 串接起來，如下

因為我們只需要最完整的 Category_id List，所以加上 MAX 和 GROUP BY
再把最前面的 “," 去掉即可得到 Category_id List了，最後的 SQL 如下

SELECT CPNT.company_id,  SUBSTR(MAX(SYS_CONNECT_BY_PATH(CPNT.category_id, ‘,’)),2) AS category_id_list
FROM
(SELECT company_id, category_id,
ROW_NUMBER() OVER(PARTITION BY company_id ORDER BY category_id) AS rn
FROM tb_company_category
ORDER BY company_id ) CPNT
START WITH CPNT.rn = 1
CONNECT BY CPNT.company_id = PRIOR CPNT.company_id
AND CPNT.rn – 1 = PRIOR CPNT.rn
GROUP BY CPNT.company_id
order by CPNT.company_id