萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

SQL Server 行轉列神技:STUFF + FOR XML PATH 將多行合併為單行的標準化實現與原理解析

將一列多行值按分組拼接為單行(如:類別 → 名稱列表),是報表開發、數據導出、ETL 轉換的高頻需求。SQL Server 2005+ 引入的 FOR XML PATH('') 配合 STUFF 函數,提供了無需 CLR、無需游標、性能優異的標準解法。本文深度解析原理、提供通用模板、對比新版 STRING_AGG、給出性能調優建議。

🎯 核心需求與示例數據

-- 源表 Table_A
Category | Name
---------|---------
AAA      | Company1
AAA      | Company2
AAA      | Company3
BBB      | Company4
BBB      | Company5

-- 目標結果
Category | Names
---------|------------------------
AAA      | Company1,Company2,Company3
BBB      | Company4,Company5

⚙️ 標準解法:STUFF + FOR XML PATH

SELECT 
    Category,
    Names = STUFF((
        SELECT ',' + Name 
        FROM Table_A 
        WHERE Category = A.Category 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM Table_A AS A
GROUP BY Category;

🔬 逐層拆解原理

1. FOR XML PATH('') — 將結果集序列化為 XML 片段

SELECT ',' + Name FROM Table_A WHERE Category = 'AAA' FOR XML PATH('');
-- 輸出:,Company1,Company2,Company3
-- 加上 TYPE 指令返回 XML 類型,再用 .value('.', 'NVARCHAR(MAX)') 提取純文本
-- 結果:,Company1,Company2,Company3

2. STUFF — 精準移除首個分隔符

STUFF(String, Start, Length, Replacement)
-- STUFF(',Company1,Company2,Company3', 1, 1, '') 
-- → 從位置 1 刪除 1 字符 (首個逗號),用空字符串替換
-- → Company1,Company2,Company3

3. GROUP BY 外層驅動分組

外層 GROUP BY Category 驅動每組執行一次子查詢,子查詢關聯外層 Category = A.Category 實現分組內拼接。

🆚 新版本原生函數:STRING_AGG (SQL Server 2017+ / Azure SQL)

-- 現代寫法:更簡潔、性能更優、原生支援排序
SELECT 
    Category,
    STRING_AGG(Name, ',') WITHIN GROUP (ORDER BY Name) AS Names
FROM Table_A
GROUP BY Category;
特性STUFF + XMLSTRING_AGG
支援版本SQL Server 2005+SQL Server 2017+ / Azure SQL
排序支援需子查詢 ORDER BY原生 WITHIN GROUP (ORDER BY ...)
性能中等 (XML 解析開銷)高 (原生聚合實現)
最大長度2GB (NVARCHAR(MAX))2GB (NVARCHAR(MAX))
NULL 處理自動忽略 NULL自動忽略 NULL

📦 通用模板庫(複製即用)

模板 1:基本拼接(無序)

SELECT 
    GroupCol,
    ConcatCol = STUFF((
        SELECT ',' + DetailCol 
        FROM SourceTable 
        WHERE GroupCol = T.GroupCol 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;

模板 2:指定排序拼接

SELECT 
    GroupCol,
    ConcatCol = STUFF((
        SELECT ',' + DetailCol 
        FROM SourceTable 
        WHERE GroupCol = T.GroupCol 
        ORDER BY SortCol -- 關鍵:子查詢加 ORDER BY
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;

模板 3:多列拼接(如:Name:Value 格式)

SELECT 
    GroupCol,
    ConcatCol = STUFF((
        SELECT ';' + Name + ':' + CAST(Value AS VARCHAR) 
        FROM SourceTable 
        WHERE GroupCol = T.GroupCol 
        FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
FROM SourceTable T
GROUP BY GroupCol;

⚡ 性能優化實戰建議

  1. 索引支撐:(GroupCol, SortCol) INCLUDE (DetailCol) 覆蓋子查詢
  2. 大分組預聚合:分組數 > 1萬、組內行數 > 1000 時,考慮先聚合到臨時表再拼接
  3. 避免在高併發 OLTP 核心鏈路使用:XML 解析有 CPU 開銷,報表/ETL/離線任務優先
  4. 長度截斷防禦:業務允許時加 LEFT(..., 4000) 或應用層截斷
  5. 升級路徑:SQL Server 2017+ 環境**直接遷移到 STRING_AGG**

📚 FOR XML PATH 進階:不只是拼接

-- 生成 JSON 片段 (SQL Server 2016+ FOR JSON)
SELECT * FROM Student FOR JSON AUTO;

-- 生成嵌套 XML 結構
SELECT 
    Category AS '@Category',
    (SELECT Name AS 'Item' FROM Table_A WHERE Category = T.Category FOR XML PATH(''), TYPE)
FROM Table_A T
GROUP BY Category
FOR XML PATH('Category'), ROOT('Categories');

選型口訣:
版本新用 STRING_AGG,版本舊用 STUFF+XML,
索引覆蓋是基石,大分組預聚合,
OLTP 核心路徑慎用,報表 ETL 好歸宿。

copyright © 萬盛學電腦網 all rights reserved