SQL Server 將欄位值串聯為字串：FOR XML PATH 技巧完整解析

需求：將資料表 Name 欄位的所有值（a, b, c, d）串聯成單一字串 a*b*c*d*。SQL Server 經典解法使用 FOR XML PATH('') 搭配子查詢。

核心語法

DECLARE @test TABLE (name VARCHAR(10));
INSERT INTO @test VALUES ('a'), ('b'), ('c'), ('d');

SELECT DISTINCT
    (SELECT CAST(name AS VARCHAR(2)) + '*' FROM @test FOR XML PATH('')) AS name
FROM @test;

輸出結果

name
--------------
a*b*c*d*

原理解析

1. FOR XML PATH('') 將每列轉為 XML 片段，PATH('') 移除預設 <row> 標籤。
2. 子查詢 SELECT CAST(name AS VARCHAR(2)) + '*' ... 產生 a*b*c*d* 字串流。
3. 外層 SELECT DISTINCT 去除重複行（因子查詢對每外層列皆執行一次，結果相同）。

進階變體：去除尾部分隔符、指定排序

-- 使用 STUFF 移除首字元（或用 SUBSTRING 移除尾端 *）
SELECT STUFF((
    SELECT '*' + name FROM @test ORDER BY name FOR XML PATH(''), TYPE
).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS ConcatenatedString;

-- 結果：a*b*c*d（無尾端 *）

• TYPE 指令回傳 XML 型別，.value('.', 'VARCHAR(MAX)') 安全轉字串（處理特殊字元編碼）。
• STUFF(..., 1, 1, '') 移除第一個分隔符。

適用版本與現代替代方案

• SQL Server 2005+ 全支援 FOR XML PATH。
• **SQL Server 2017 / Azure SQL Database** 起原生支援 STRING_AGG(expression, separator) [WITHIN GROUP (ORDER BY ...)]，語法更直觀、效能更佳。

總結

FOR XML PATH('') 是舊版本串聯字串的標準技巧；新版本請優先使用 STRING_AGG。理解其 XML 序列化本質，有助於處理特殊字元、排序與效能優化。