將一列多行值按分組拼接為單行(如:類別 → 名稱列表),是報表開發、數據導出、ETL 轉換的高頻需求。SQL Server 2005+ 引入的 FOR XML PATH('') 配合 STUFF 函數,提供了無需 CLR、無需游標、性能優異的標準解法。本文深度解析原理、提供通用模板、對比新版 STRING_AGG、給出性能調優建議。
-- 源表 Table_A Category | Name ---------|--------- AAA | Company1 AAA | Company2 AAA | Company3 BBB | Company4 BBB | Company5 -- 目標結果 Category | Names ---------|------------------------ AAA | Company1,Company2,Company3 BBB | Company4,Company5
SELECT
Category,
Names = STUFF((
SELECT ',' + Name
FROM Table_A
WHERE Category = A.Category
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table_A AS A
GROUP BY Category;
SELECT ',' + Name FROM Table_A WHERE Category = 'AAA' FOR XML PATH('');
-- 輸出:,Company1
,Company2
,Company3
-- 加上 TYPE 指令返回 XML 類型,再用 .value('.', 'NVARCHAR(MAX)') 提取純文本
-- 結果:,Company1,Company2,Company3
STUFF(String, Start, Length, Replacement)
-- STUFF(',Company1,Company2,Company3', 1, 1, '')
-- → 從位置 1 刪除 1 字符 (首個逗號),用空字符串替換
-- → Company1,Company2,Company3
外層 GROUP BY Category 驅動每組執行一次子查詢,子查詢關聯外層 Category = A.Category 實現分組內拼接。
-- 現代寫法:更簡潔、性能更優、原生支援排序
SELECT
Category,
STRING_AGG(Name, ',') WITHIN GROUP (ORDER BY Name) AS Names
FROM Table_A
GROUP BY Category;
| 特性 | STUFF + XML | STRING_AGG |
|---|---|---|
| 支援版本 | SQL Server 2005+ | SQL Server 2017+ / Azure SQL |
| 排序支援 | 需子查詢 ORDER BY | 原生 WITHIN GROUP (ORDER BY ...) |
| 性能 | 中等 (XML 解析開銷) | 高 (原生聚合實現) |
| 最大長度 | 2GB (NVARCHAR(MAX)) | 2GB (NVARCHAR(MAX)) |
| NULL 處理 | 自動忽略 NULL | 自動忽略 NULL |
SELECT
GroupCol,
ConcatCol = STUFF((
SELECT ',' + DetailCol
FROM SourceTable
WHERE GroupCol = T.GroupCol
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;
SELECT
GroupCol,
ConcatCol = STUFF((
SELECT ',' + DetailCol
FROM SourceTable
WHERE GroupCol = T.GroupCol
ORDER BY SortCol -- 關鍵:子查詢加 ORDER BY
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;
SELECT
GroupCol,
ConcatCol = STUFF((
SELECT ';' + Name + ':' + CAST(Value AS VARCHAR)
FROM SourceTable
WHERE GroupCol = T.GroupCol
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;
(GroupCol, SortCol) INCLUDE (DetailCol) 覆蓋子查詢LEFT(..., 4000) 或應用層截斷-- 生成 JSON 片段 (SQL Server 2016+ FOR JSON)
SELECT * FROM Student FOR JSON AUTO;
-- 生成嵌套 XML 結構
SELECT
Category AS '@Category',
(SELECT Name AS 'Item' FROM Table_A WHERE Category = T.Category FOR XML PATH(''), TYPE)
FROM Table_A T
GROUP BY Category
FOR XML PATH('Category'), ROOT('Categories');
選型口訣:
版本新用 STRING_AGG,版本舊用 STUFF+XML,
索引覆蓋是基石,大分組預聚合,
OLTP 核心路徑慎用,報表 ETL 好歸宿。