將同群組的多列字串值串聯成單一逗號分隔字串,是報表與資料轉換的常見需求。SQL Server 2005+ 提供的 FOR XML PATH('') 搭配 STUFF 函數,是最高效、最標準的做法。本文提供兩種寫法並解析原理。
DECLARE @T1 TABLE (
UserID INT,
UserName NVARCHAR(50),
CityName NVARCHAR(50)
);
INSERT INTO @T1 VALUES
(1, 'a', '上海'),
(2, 'b', '北京'),
(3, 'c', '上海'),
(4, 'd', '北京'),
(5, 'e', '上海');
SELECT
CityName,
STUFF((
SELECT ',' + UserName
FROM @T1 sub
WHERE sub.CityName = A.CityName
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS UserList
FROM @T1 A
GROUP BY CityName;
FOR XML PATH(''):將子查詢每列結果串聯為無標籤 XML 片段,如 ,a,c,e。TYPE 指示回傳 xml 型別,避免實體字元編碼(如 <)。.value('.', 'NVARCHAR(MAX)'):將 XML 轉為字串。STUFF(str, 1, 1, ''):移除首字元(前導逗號)。SELECT B.CityName,
LEFT(B.UserList, LEN(B.UserList) - 1) AS UserList
FROM (
SELECT CityName,
(SELECT UserName + ',' FROM @T1 WHERE CityName = A.CityName FOR XML PATH('')) AS UserList
FROM @T1 A
GROUP BY CityName
) B;
邏輯相同,但需額外包一層衍生表處理尾端逗號,可讀性與效能略遜於方法一。
STUFF((
SELECT ',' + ColumnName
FROM TableName
WHERE GroupCol = OuterTable.GroupCol
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
GroupCol 有索引,子查詢可快速定位群組。STRING_AGG (SQL Server 2017+):SELECT CityName, STRING_AGG(UserName, ',') FROM @T1 GROUP BY CityName; — 原生函數,語法最簡潔、效能最強。
輸出:
CityName UserList
北京 b,d
上海 a,c,e
掌握此技巧,輕鬆應對「列轉行」彙總報表需求。