萬盛學電腦網

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

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

將同群組的多列字串值串聯成單一逗號分隔字串,是報表與資料轉換的常見需求。SQL Server 2005+ 提供的 FOR XML PATH('') 搭配 STUFF 函數,是最高效、最標準的做法。本文提供兩種寫法並解析原理。

測試資料準備

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

INSERT INTO @T1 VALUES
(1, 'a', '上海'),
(2, 'b', '北京'),
(3, 'c', '上海'),
(4, 'd', '北京'),
(5, 'e', '上海');

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

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

核心解析

  1. FOR XML PATH(''):將子查詢每列結果串聯為無標籤 XML 片段,如 ,a,c,e。
  2. TYPE 指示回傳 xml 型別,避免實體字元編碼(如 <)。
  3. .value('.', 'NVARCHAR(MAX)'):將 XML 轉為字串。
  4. STUFF(str, 1, 1, ''):移除首字元(前導逗號)。

方法二:子查詢 + 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;

邏輯相同,但需額外包一層衍生表處理尾端逗號,可讀性與效能略遜於方法一。

通用重用片段

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

效能提示

  • 確保 GroupCol 有索引,子查詢可快速定位群組。
  • 大量資料時,考慮使用 STRING_AGG (SQL Server 2017+):
    SELECT CityName, STRING_AGG(UserName, ',') FROM @T1 GROUP BY CityName; — 原生函數,語法最簡潔、效能最強。
  • 避免在大表上使用標量函數或游標迴圈串聯字串。

結果展示

合併結果示意圖

輸出:

CityName  UserList
北京      b,d
上海      a,c,e

掌握此技巧,輕鬆應對「列轉行」彙總報表需求。

copyright © 萬盛學電腦網 all rights reserved