SQL Server 將欄位多列值串聯為單一字串：STUFF + FOR XML PATH 實戰

需求

表 Table_A(Category, Name)：

Category | Name
AAA      | Company1
AAA      | Company2
AAA      | Company3
BBB      | Company4
BBB      | Company5

期望輸出：

Category | Names
AAA      | Company1,Company2,Company3
BBB      | Company4,Company5

標準解法：STUFF + FOR XML PATH('')

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;

關鍵函數解析

FOR XML PATH('')

• 將查詢結果序列化為 XML 片段，PATH('') 移除預設 <row> 標籤。
• SELECT ',' + Name ... FOR XML PATH('') 產生 ,Company1,Company2,Company3 (前導逗號)。
• 加 TYPE 指示回傳 XML 型別，避免實體編碼 (< 等)。

STUFF(character_expression, start, length, replacement)

• 刪除 start 位置起 length 字元，插入 replacement。
• STUFF(xml_result, 1, 1, '')：移除第 1 個字元 (前導逗號)，插入空字串 → Company1,Company2,Company3。

.value('.', 'NVARCHAR(MAX)')

• 將 XML 型別轉為標量字串，. 代表當前節點內容。

STUFF 範例速查

SELECT STUFF('abcdefg', 1, 0, '1234'); -- 1234abcdefg (插入)
SELECT STUFF('abcdefg', 1, 1, '1234'); -- 1234bcdefg  (替換第1字元)
SELECT STUFF('abcdefg', 2, 1, '1234'); -- a1234cdefg  (替換第2字元)
SELECT STUFF('abcdefg', 2, 2, '1234'); -- a1234defg   (替換第2-3字元)

FOR XML PATH 進階示範

-- 基礎表
CREATE TABLE Stu_Course (StuName VARCHAR(20), Course VARCHAR(20));
INSERT INTO Stu_Course VALUES ('張三','數學'),('張三','英文'),('李四','物理');

-- 查詢
SELECT StuName, Course FROM Stu_Course FOR XML PATH;
-- 輸出: 張三數學...

-- PATH('') 移除 row 標籤
SELECT StuName, Course FROM Stu_Course FOR XML PATH('');
-- 輸出: 張三數學張三英文...

SQL Server 2017+ 新選擇：STRING_AGG

SELECT 
  Category,
  STRING_AGG(Name, ',') WITHIN GROUP (ORDER BY Name) AS Names
FROM Table_A
GROUP BY Category;

• 原生聚合函數，語法更簡潔、效能更優 (無 XML 解析開銷)。
• WITHIN GROUP (ORDER BY ...) 控制串聯順序。
• 舊版相容專案仍用 STUFF + FOR XML PATH。

效能提示

• 子查詢相關欄位 (Category) 應有索引。
• 大量群組時，考慮先聚合到暫存表再串聯，避免重複掃描。
• FOR XML PATH 在極大字串 (> 2GB) 會截斷，STRING_AGG 同受 NVARCHAR(MAX) 限制。

結語

STUFF + FOR XML PATH('') 是 SQL Server 2005~2016 串聯字串的標準模式，原理為「XML 序列化 → 移除前導分隔符」。SQL 2017+ 建議直接使用 STRING_AGG。掌握兩種寫法，依版本選擇最佳方案。