SQL Server 將多列資料合併為單列逗號分隔字串 (STUFF + FOR XML PATH)

常見需求：將群組內的多筆資料合併成單一字串，欄位值以逗號分隔。SQL Server 2005+ 提供 FOR XML PATH('') 搭配 STUFF 移除前導分隔符，是最高效的純 SQL 解法。

測試資料準備

DECLARE @T1 TABLE (UserID INT, UserName NVARCHAR(50), CityName NVARCHAR(50));
INSERT INTO @T1 VALUES
(1, N'a', N'上海'), (2, N'b', N'北京'),
(3, N'c', N'上海'), (4, N'd', N'北京'),
(5, N'e', N'上海');

方法一：STUFF + FOR XML PATH (推薦，效能最佳)

SELECT
    CityName,
    STUFF((
        SELECT ',' + UserName
        FROM @T1 sub
        WHERE sub.CityName = t.CityName
        FOR XML PATH('')
    ), 1, 1, '') AS UserList
FROM @T1 t
GROUP BY CityName;

方法二：子查詢 + LEFT/LEN 移除尾逗號

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;

核心函數解析

• FOR XML PATH('')：將結果集序列化為無標籤 XML 字串，實現列轉行串聯。
• STUFF(str, 1, 1, '')：刪除字串第 1 碼長度 1 的字元 (即前導逗號)。語法：STUFF(字串, 起始位置, 長度, 替換字串)。

通用範本

SELECT
    分群欄位,
    STUFF((
        SELECT ',' + 目標欄位
        FROM 表名 子查詢
        WHERE 子查詢.分群欄位 = 主查詢.分群欄位
        FOR XML PATH('')
    ), 1, 1, '') AS 合併結果
FROM 表名 主查詢
GROUP BY 分群欄位;

適用版本與效能

• SQL Server 2005 及以上版本皆支援。
• 大量資料時，確保分群欄位與篩選欄位有索引，避免子查詢重複掃描。
• SQL Server 2017+ 可改用 STRING_AGG(目標欄位, ',') WITHIN GROUP (ORDER BY 排序欄位)，語法更簡潔且效能更優。