SQL Server 字符串聚合：STUFF + FOR XML PATH 實現分組字段合並（兼容 2005+）

場景

表 Body(ID, BODY) 有重復 ID，需按 ID 分組將 BODY 值用逗號拼成單行：

ID | BODY
1  | aaaa
2  | bbbb
1  | cccccc
3  | ddddd
-- 期望結果
1 | aaaa,cccccc
2 | bbbb
3 | ddddd

經典方案：STUFF + FOR XML PATH('')

SELECT DISTINCT a.ID,
       STUFF((
           SELECT ',' + BODY
           FROM Body
           WHERE ID = a.ID
           FOR XML PATH(''), TYPE
       ).value('.','NVARCHAR(MAX)'), 1, 1, '') AS BODY
FROM Body a;

關鍵點拆解

1. FOR XML PATH('')：將子查詢結果按行拼接，無標籤，產生 ,aaaa,cccccc 形式的 XML 片段。
2. .value('.','NVARCHAR(MAX)')：將 XML 片段轉為標量字符串（兼容特殊字符 < > & 自動實體化）。
3. STUFF(..., 1, 1, '')：刪除首字符逗號。
4. DISTINCT：去重外層分組鍵。

SQL Server 2017+ 現代寫法：STRING_AGG

SELECT ID, STRING_AGG(BODY, ',') AS BODY
FROM Body
GROUP BY ID;

更簡潔、性能更強、支持 WITHIN GROUP (ORDER BY ...) 排序。

大數據量優化建議

• 在分組鍵 ID 上建立索引。
• 若聚合後長度超 4000 字符，確保目標列類型為 NVARCHAR(MAX) / VARCHAR(MAX)。
• 極高頻場景考慮 CLR 聚合或物化視圖預計算。

結語

STUFF + FOR XML PATH 是 2005-2016 時代的字符串聚合標準解法，至今仍廣泛運行於存量系統。新項目請直接使用 STRING_AGG，享受原生優化與簡潔語法。