SQL 進階技巧：用 STUFF + FOR XML PATH 實現分組字串聚合

目標結果：按 userpwd 分組，username 用逗號拼接。

核心語句

SELECT 
    userpwd,
    usernames = STUFF((
        SELECT ',' + username 
        FROM tuser t1 
        WHERE t1.userpwd = t.userpwd 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM tuser t
GROUP BY userpwd;

逐層拆解

1. FOR XML PATH('') —— 將行轉為 XML 片段

SELECT ',' + username 
FROM tuser 
WHERE userpwd = '123' 
FOR XML PATH('');

輸出：,Alice,Bob（每行前自動加逗號，無 XML 標籤）。

2. STUFF(..., 1, 1, '') —— 移除首個多餘分隔符

語法：STUFF(字串, 起始位置, 刪除長度, 替換字串)

STUFF(',Alice,Bob', 1, 1, '')  -- 刪除第 1 字符 (逗號) → 'Alice,Bob'

3. .value('.', 'NVARCHAR(MAX)') —— 安全處理特殊字符

FOR XML PATH 會將 &、<、> 轉義為 & 等。加上 , TYPE 返回 XML 型別，再用 .value('.', 'NVARCHAR(MAX)') 還原為純文本，避免雙重轉義。

4. 外層 GROUP BY —— 確保每組僅輸出一行

外層按 userpwd 分組，子查詢以當前組值為相關條件，實現分組聚合。

完整可運行模板（含建表測試數據）

-- 1. 建表與測試數據
CREATE TABLE #tuser (userpwd VARCHAR(20), username NVARCHAR(50));
INSERT INTO #tuser VALUES 
('123', N'Alice'), ('123', N'Bob'),
('456', N'Charlie'), ('456', N'David'), ('456', N'Eve');

-- 2. 分組拼接查詢
SELECT 
    userpwd,
    usernames = STUFF((
        SELECT ',' + username 
        FROM #tuser t1 
        WHERE t1.userpwd = t.userpwd 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM #tuser t
GROUP BY userpwd;

-- 3. 清理
DROP TABLE #tuser;

效能優化關鍵點

• 索引支撐：在 userpwd 上建立非叢集索引，子查詢可直接 Index Seek。
• 避免大字串溢位：SQL Server 單字串上限 2GB，極大組別建議分頁或用 STRING_AGG（2017+）。
• 並行度：大表時外層 GROUP BY 可並行，子查詢為相關子查詢通常串行，整體仍優於游標/WHILE 迴圈。

版本升級建議

-- SQL Server 2017+ 現代寫法
SELECT userpwd, STRING_AGG(username, ',') AS usernames
FROM tuser
GROUP BY userpwd;

常見坑與避雷指南

坑 1：忘記 TYPE 導致特殊字符雙重轉義

❌ FOR XML PATH('') → 輸出 &
✅ FOR XML PATH(''), TYPE + .value('.', 'NVARCHAR(MAX)')

坑 2：NULL 值導致整組結果為 NULL

用 ISNULL(username, '') 或 COALESCE 兜底。

坑 3：未指定 ORDER BY 導致拼接順序不穩定

子查詢加 ORDER BY username 保證確定性。

坑 4：超長字串截斷

檢查 NVARCHAR(MAX) 是否足夠，極端情況下分批處理。

擴展應用：多欄位拼接、條件過濾、去重

-- 多欄位：username + '(' + role + ')'
STUFF((
    SELECT ',' + username + '(' + role + ')'
    FROM tuser t1
    WHERE t1.userpwd = t.userpwd
      AND t1.status = 1  -- 條件過濾
    GROUP BY username, role  -- 去重
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

總結

STUFF + FOR XML PATH 是 SQL Server 2016 以前實現分組字串聚合的標準最優解。掌握其「XML 序列化 → 移除首分隔符 → 類型還原」三步曲，配合索引與 TYPE 安全轉換，即可在存儲過程、視圖、報表查詢中穩定高效使用。若環境已升級至 2017+，請無腦遷移至 STRING_AGG，語法更簡潔、效能更強、可維護性更高。