SQL Server 多列合併為單一字串：STUFF + FOR XML PATH 最佳實踐

需求場景

將同一群組下多筆資料的特定欄位（如原因代碼、標籤、郵件地址）串聯成單一字串，以分隔符號分隔。典型應用：訂單多商品名稱合併、使用者多角色標籤聚合、錯誤代碼彙總。

傳統方法：標量函數 + 迴圈（不推薦）

CREATE FUNCTION dbo.ConcatReasons(@Standard VARCHAR(100))
RETURNS VARCHAR(8000)
AS BEGIN
    DECLARE @Result VARCHAR(8000) = ''
    SELECT @Result = @Result + ',' + Reason
    FROM tb WHERE Standard = @Standard
    RETURN STUFF(@Result, 1, 1, '')
END

缺點：逐列處理、無法平行化、效能差、無法利用索引、並發下易發生阻塞。

現代最佳解法：FOR XML PATH + STUFF

SELECT 
    Standard,
    Amount,
    Variation,
    Status,
    STUFF((
        SELECT ',' + Reason
        FROM tb t2
        WHERE t2.Standard = t1.Standard
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS Reasons
FROM tb t1
GROUP BY Standard, Amount, Variation, Status

關鍵點解析

• FOR XML PATH('')：產生無標籤 XML 片段，自動串聯
• TYPE 指令：回傳 XML 型別，避免特殊字元（& < >）被編碼
• .value('.', 'VARCHAR(MAX)')：將 XML 轉回字串
• STUFF(..., 1, 1, '')：移除首字分隔符

進階：STRING_AGG（SQL Server 2017+）

SELECT 
    Standard,
    STRING_AGG(Reason, ',') WITHIN GROUP (ORDER BY Reason) AS Reasons
FROM tb
GROUP BY Standard

原生聚合函數，語法簡潔、效能最佳、支援排序、自動處理 NULL。首選方案。

效能比較與建議

實務注意事項

• 結果長度可能超過 8000 字元 → 使用 VARCHAR(MAX) / NVARCHAR(MAX)
• 大量資料群組串聯 → 考慮中繼表預聚合或應用層處理
• 特殊字元處理：FOR XML PATH 需 TYPE + .value 防編碼
• 排序需求：WITHIN GROUP (ORDER BY ...) 或子查詢加 ORDER BY

完整範例：訂單商品名稱合併

SELECT 
    o.OrderID,
    o.CustomerName,
    STRING_AGG(p.ProductName, '; ') WITHIN GROUP (ORDER BY p.ProductName) AS Products
FROM Orders o
JOIN OrderDetails od ON o.OrderID = od.OrderID
JOIN Products p ON od.ProductID = p.ProductID
WHERE o.OrderDate >= '2024-01-01'
GROUP BY o.OrderID, o.CustomerName