將同群組多行字串合併為單行逗號分隔是常見報表需求。本文詳解經典 STUFF + FOR XML PATH('') 模式、替代 LEFT + LEN 模式,並介紹 SQL Server 2017+ 內建 STRING_AGG 的現代寫法與效能對比。
DECLARE @T1 TABLE (UserID INT, UserName NVARCHAR(50), CityName NVARCHAR(50));
INSERT @T1 VALUES
(1,'a','上海'),(2,'b','北京'),(3,'c','上海'),
(4,'d','北京'),(5,'e','上海');
目標:按 CityName 分組,UserName 以逗號合併
SELECT
CityName,
STUFF((
SELECT ',' + UserName
FROM @T1 sub
WHERE sub.CityName = t.CityName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS Users
FROM @T1 t
GROUP BY CityName;
FOR XML PATH(''):將子查詢每行轉為 `TYPE:回傳 XML 型別,避免實體字元編碼(< > &).value('.', 'NVARCHAR(MAX)'):提取 XML 純文字STUFF(..., 1, 1, ''):移除首字元多餘逗號SELECT
CityName,
LEFT(UserList, LEN(UserList) - 1) AS Users
FROM (
SELECT
CityName,
(SELECT UserName + ','
FROM @T1
WHERE CityName = t.CityName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)') AS UserList
FROM @T1 t
GROUP BY CityName
) d;
SELECT
CityName,
STRING_AGG(UserName, ',') WITHIN GROUP (ORDER BY UserName) AS Users
FROM @T1
GROUP BY CityName;
WITHIN GROUP (ORDER BY ...) 排序DISTINCT:STRING_AGG(DISTINCT UserName, ',')| 方案 | CPU 時間 | 邏輯讀取 | 備註 |
|---|---|---|---|
| STUFF + FOR XML PATH | ~1200ms | ~45,000 | XML 解析開銷大 |
| LEFT + LEN + FOR XML PATH | ~1150ms | ~44,000 | 相近 |
| STRING_AGG | ~300ms | ~12,000 | 原生實作,無 XML 開銷 |
STRING_AGG(CONCAT(UserName, ':', Email), ';') -- 2017+
-- 或 XML 方案
STUFF((SELECT ';' + UserName + ':' + Email FROM ... FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')
STRING_AGG(CASE WHEN Active = 1 THEN UserName END, ',') -- 僅合併啟用用戶
-- 限制長度 4000 字元
LEFT(STRING_AGG(UserName, ','), 4000) + CASE WHEN LEN(STRING_AGG(UserName, ',')) > 4000 THEN '...' ELSE '' END
STRING_AGG 自動忽略 NULL;XML 方案需 ISNULL(UserName,'')ORDER BY;STRING_AGG 用 WITHIN GROUPFOR XML PATH 實體編碼:資料含 <>& 時**必須**加 TYPE + .value(),否則會被編碼為 < 等NVARCHAR(MAX) 2GB,但應用層可能有限制(如 SSRS 4000 字元)SQL Server 2017+ 環境**首選 STRING_AGG**;舊版本用 STUFF + FOR XML PATH('', TYPE).value() 經典模式。理解 XML 方案原理,有助於維護存量代碼與跨版本相容。