您现在的位置：程式碼之家 >> 數據庫知識 > >> SQL Server 資料庫 >> 關於SqlServer

SQL Server 將多列資料合併為單一字串：STUFF + FOR XML PATH 完整教學

編輯：關於SqlServer

應用場景

將同一群組內的多筆資料列，彙總為單一字串，欄位值以分隔符 (如逗號、分號) 區隔。常見於：標籤彙總、權限清單、明細匯總顯示。

核心技術：FOR XML PATH('') + STUFF

STUFF((
    SELECT ',' + ColumnName
    FROM TableName
    WHERE GroupCondition = Outer.GroupCondition
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

關鍵點解析

FOR XML PATH('')：將結果集序列化為 XML 片段，PATH('') 表示不產生包裹元素，直接輸出字串。
SELECT ',' + ColumnName：每個值前加分隔符，產生 ,Value1,Value2,Value3。
.value('.', 'NVARCHAR(MAX)')：將 XML 型別轉為字串 (含 TYPE 時需此步驟)。
STUFF(..., 1, 1, '')：移除第一個字元 (多餘的前置分隔符)。

完整範例：依城市彙總使用者名稱

建立測試資料

DECLARE @T1 TABLE (
    UserID INT,
    UserName NVARCHAR(50),
    CityName NVARCHAR(50)
);

INSERT INTO @T1 VALUES
(1, N'小明', N'上海'),
(2, N'小紅', N'北京'),
(3, N'小強', N'上海'),
(4, N'小美', N'北京'),
(5, N'小李', N'上海');

寫法一：子查詢 + GROUP BY (最常用、效能佳)

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

結果：

CityName	UserList
上海	小明,小強,小李
北京	小紅,小美

寫法二：CROSS APPLY (可讀性高、支援複雜排序)

SELECT
    main.CityName,
    ca.UserList
FROM @T1 main
CROSS APPLY (
    SELECT STUFF((
        SELECT ',' + UserName
        FROM @T1 sub
        WHERE sub.CityName = main.CityName
        ORDER BY UserName -- 可在這裡排序
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS UserList
) ca
GROUP BY main.CityName, ca.UserList;

寫法二 (舊版相容)：LEFT + 子查詢

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

進階應用

1. 去重彙總

STUFF((
    SELECT DISTINCT ',' + UserName -- 加 DISTINCT
    FROM @T1 sub
    WHERE sub.CityName = main.CityName
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

2. 自訂排序

STUFF((
    SELECT ',' + UserName
    FROM @T1 sub
    WHERE sub.CityName = main.CityName
    ORDER BY UserID DESC -- 依 ID 遞減
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

3. 多欄位合併 (如「姓名:年齡」)

STUFF((
    SELECT ',' + UserName + ':' + CAST(Age AS VARCHAR(3))
    FROM @T1 sub
    WHERE sub.CityName = main.CityName
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

4. 避免 XML 特殊字元被編碼 (< > &)

若欄位值可能包含 < > &，需改用 FOR XML PATH 的 TYPE 配合 .value (如上範例已示範)，或使用 REPLACE 處理。

SQL Server 2017+ 新語法：STRING_AGG (原生、更簡潔、效能更好)

SELECT
    CityName,
    STRING_AGG(UserName, ',') WITHIN GROUP (ORDER BY UserName) AS UserList
FROM @T1
GROUP BY CityName;

優點：原生函數、無 XML 開銷、支援 WITHIN GROUP (ORDER BY ...) 內建排序。
相容性：SQL Server 2017 (14.x) 及以上、Azure SQL Database。

效能比較與建議

方法	適用版本	效能	建議
STRING_AGG	2017+	⭐⭐⭐⭐⭐	首選，原生最佳化
STUFF + FOR XML PATH	2005+	⭐⭐⭐	舊版本唯一選擇，加 `TYPE` + `.value` 防編碼
CLR 聚合函數	2005+	⭐⭐⭐⭐	極大資料量、複雜邏輯時考慮
游標/迴圈	所有	⭐	嚴禁生產環境，效能極差

總結

SQL Server 2005~2016：使用 STUFF + FOR XML PATH('') + TYPE + .value 模式，穩健、相容性佳。
SQL Server 2017+：直接用 STRING_AGG，語法簡潔、效能最佳。
記得處理 **前置分隔符移除 (STUFF/LEFT)**、**特殊字元編碼**、**排序**、**去重** 等細節。