Oracle 單欄多行轉單行顯示：SYS_CONNECT_BY_PATH 實戰技巧

Oracle 單欄多行轉單行顯示：SYS_CONNECT_BY_PATH 實戰技巧

需求：將單一欄位的多行查詢結果**串聯為單一字串**，行間以分隔符 (如 `;`) 分隔。Oracle 11g+ 推薦使用 LISTAGG，但舊版本可利用階層查詢 SYS_CONNECT_BY_PATH 達成。

核心 SQL

SELECT SUBSTR(SYS_CONNECT_BY_PATH(dutywork, ';'), 2) AS all_duties
  FROM (SELECT dutywork, ROWNUM rn FROM t_test)
 WHERE CONNECT_BY_ISLEAF = 1
 START WITH rn = 1
 CONNECT BY PRIOR rn = rn - 1;

機制解析

1. 內層查詢產生 ROWNUM 作為層級序號 (1,2,3...)。
2. START WITH rn=1 從第一行開始。
3. CONNECT BY PRIOR rn = rn-1 串聯成鏈：1→2→3...。
4. SYS_CONNECT_BY_PATH(col, ';') 遞迴拼接路徑：;val1;val2;val3。
5. CONNECT_BY_ISLEAF=1 只取葉節點 (最後一行) 得到完整路徑。
6. SUBSTR(..., 2) 移除前導分隔符。

現代替代方案 (11gR2+)

-- 標準 SQL，效能更好、語法更清晰
SELECT LISTAGG(dutywork, ';') WITHIN GROUP (ORDER BY rn) AS all_duties
  FROM (SELECT dutywork, ROWNUM rn FROM t_test);

其他常見串聯方法對比

效能提醒

• SYS_CONNECT_BY_PATH 屬於**串列遞迴**，資料量超過數千行會明顯變慢，建議改用 LISTAGG 或自訂聚合函數。
• 19c LISTAGG(...) ON OVERFLOW TRUNCATE '...' WITH COUNT 可防止 4000 位元組限制報錯。