Oracle SYS_CONNECT_BY_PATH 實現行轉列（分組字串聚合）

需求

將同群組的多行字串合併為單一行，以指定分隔符分隔。例如：

-- 原始資料
A  B
1  我
1  們
2  一
2  起

-- 期望結果
A  B
1  我,們
2  一,起

解決方案：SYS_CONNECT_BY_PATH + 分析函數

SELECT a,
       LTRIM(MAX(SYS_CONNECT_BY_PATH(b, ',')), ',') AS b
FROM (
  SELECT b, a,
         ROW_NUMBER() OVER (PARTITION BY a ORDER BY b DESC) AS rn
  FROM test
)
START WITH rn = 1
CONNECT BY rn - 1 = PRIOR rn
   AND a = PRIOR a
GROUP BY a;

原理解析

1. ROW_NUMBER：為每個群組內的行按排序鍵（此處 b DESC）分配遞增序號 rn（1,2,3...）。
2. START WITH rn = 1：從每組的最後一行（遞歸起點）開始。
3. CONNECT BY rn-1 = PRIOR rn：遞歸向前串聯（rn=2 接 rn=1，rn=3 接 rn=2...）。
4. SYS_CONNECT_BY_PATH(b, ',')：沿路徑將 b 以逗號串聯，產生 ,我、,我,們 等中間結果。
5. MAX + LTRIM：取最長路徑（完整串聯），去除前導分隔符。

適用版本與替代方案

• Oracle 11g R2+：建議直接使用 LISTAGG(b, ',') WITHIN GROUP (ORDER BY b)，語法簡潔、效能更好。
• 10g/11g R1：SYS_CONNECT_BY_PATH 是經典變通寫法。
• 12c+：亦可用 JSON_ARRAYAGG、XMLAGG 等新功能。

效能提示

• 遞歸查詢在大數據量群組下可能較慢，建議配合索引 (a, b) 加速排序與分組。
• 若群組行數極大（>1000），考慮改用 PL/SQL 迴圈或 XMLAGG 避免遞歸深度限制。