將相同分組鍵的多行字串值合併為單行 (逗號分隔/自定義分隔符) 是報表開發常見需求。本文對比三種主流實現:`FOR XML PATH` (通用)、`STRING_AGG` (2017+ 原生)、自定義標量函數 (舊版相容),並給效能建議。
CREATE TABLE #Demo (Code VARCHAR(10), Txt NVARCHAR(50));
INSERT #Demo VALUES
('102', N'科學'), ('102', N'數學'), ('102', N'數學1'),
('101', N'美術'), ('101', N'體育'), ('101', N'體育1');
SELECT
Code,
STUFF((
SELECT ',' + Txt
FROM #Demo d2
WHERE d2.Code = d1.Code
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS MergedTxt
FROM #Demo d1
GROUP BY Code;
原理:子查詢生成 `
SELECT Code, STRING_AGG(Txt, ',') WITHIN GROUP (ORDER BY Txt) AS MergedTxt
FROM #Demo
GROUP BY Code;
CREATE FUNCTION dbo.ConcatByCode (@Code VARCHAR(20))
RETURNS NVARCHAR(MAX)
AS
BEGIN
DECLARE @Result NVARCHAR(MAX) = N'';
SELECT @Result += ',' + Txt FROM #Demo WHERE Code = @Code;
RETURN STUFF(@Result, 1, 1, '');
END
SELECT Code, dbo.ConcatByCode(Code) AS MergedTxt
FROM #Demo
GROUP BY Code;
**缺點**:逐行執行函數 (RBAR)、無法平行化、計畫快取膨脹,**生產環境大表嚴禁使用**。
| 方法 | 耗時 | CPU | 邏輯讀取 | 平行度 |
|---|---|---|---|---|
| FOR XML PATH | ~2.3 秒 | 高 | 高 | 單執行緒 |
| STRING_AGG | ~0.18 秒 | 低 | 低 | 支援平行 |
| 標量函數 | ~45 秒 | 極高 | 極高 | 單執行緒 |
-- STRING_AGG 進階
SELECT Code,
STRING_AGG(DISTINCT Txt, '; ') WITHIN GROUP (ORDER BY Txt) AS Merged
FROM #Demo
GROUP BY Code;
-- XML PATH 進階 (去重 + 排序 + 長度限制 4000)
SELECT Code,
LEFT(STUFF((
SELECT '; ' + Txt
FROM (SELECT DISTINCT Txt FROM #Demo d2 WHERE d2.Code = d1.Code) d3
ORDER BY Txt FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 2, ''), 4000) AS Merged
FROM #Demo d1
GROUP BY Code;
**選型建議**:2017+ 必用 `STRING_AGG`;2005-2016 用 `FOR XML PATH` 並加適當索引 (`Code, Txt`);避免標量函數處理集合操作。