SQL Server 多行合併為單行並分組統計：XML PATH 與 STUFF 兩大經典實戰

場景

表 test(code, values, count) 需按 code 分組，將 values 以逗號合併，並對 count 求和。

測試資料

CREATE TABLE test(code VARCHAR(50), values VARCHAR(10), cnt INT);
INSERT INTO test VALUES
('001','aa',1),('001','bb',2),
('002','aaa',4),('002','bbb',5),('002','ccc',3);

方法一：FOR XML PATH + STUFF（通用、SQL 2005+）

SELECT 
    t1.code,
    STUFF((
        SELECT ',' + t2.values
        FROM test t2
        WHERE t2.code = t1.code
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS merged_values,
    SUM(t1.cnt) AS total_cnt
FROM test t1
GROUP BY t1.code;

• FOR XML PATH('') 產生 ,aa,bb。
• .value('.', 'NVARCHAR(MAX)') 取純文本（避免實體編碼）。
• STUFF(..., 1, 1, '') 移除首個逗號。

方法二：CROSS APPLY + XML（舊版寫法，等價）

SELECT 
    a.code,
    STUFF(b.xml_col.value('/R[1]', 'NVARCHAR(MAX)'), 1, 1, '') AS merged_values,
    a.total_cnt
FROM (
    SELECT code, SUM(cnt) AS total_cnt
    FROM test
    GROUP BY code
) a
CROSS APPLY (
    SELECT (SELECT ',' + values FROM test WHERE code = a.code FOR XML PATH(''), ROOT('R'), TYPE) AS xml_col
) b;

方法三：STRING_AGG（SQL 2017+，最簡潔）

SELECT 
    code,
    STRING_AGG(values, ',') AS merged_values,
    SUM(cnt) AS total_cnt
FROM test
GROUP BY code;

效能對比（萬行級測試）

避坑指南

1. 長度截斷：FOR XML PATH 預設 2033 字元限制，需用 TYPE + .value() 繞過。
2. 特殊字元：值含 &、<、> 會被 XML 編碼，.value() 會自動解碼；若用 STRING_AGG 無此問題。
3. 排序：合併順序不穩定，需在子查詢加 ORDER BY seq。
4. NULL 值：STRING_AGG 自動忽略 NULL；XML 法需自行 WHERE values IS NOT NULL。

查詢結果

code  merged_values       total_cnt
001   aa,bb               3
002   aaa,bbb,ccc         12

按版本選擇：SQL 2017+ 直用 STRING_AGG；舊版首選 FOR XML PATH + STUFF，穩健通用。