萬盛學電腦網

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

SQL Server 將多行合併為單行逗號分隔:STUFF + FOR XML PATH 經典模式與現代 STRING_AGG

將同群組多行字串合併為單行逗號分隔是常見報表需求。本文詳解經典 STUFF + FOR XML PATH('') 模式、替代 LEFT + LEN 模式,並介紹 SQL Server 2017+ 內建 STRING_AGG 的現代寫法與效能對比。

範例資料

DECLARE @T1 TABLE (UserID INT, UserName NVARCHAR(50), CityName NVARCHAR(50));
INSERT @T1 VALUES 
(1,'a','上海'),(2,'b','北京'),(3,'c','上海'),
(4,'d','北京'),(5,'e','上海');

目標:按 CityName 分組,UserName 以逗號合併

方案一:STUFF + FOR XML PATH('')(經典、全版本支援)

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

機制拆解

  1. FOR XML PATH(''):將子查詢每行轉為 `值`,空路徑名移除標籤,產生連續字串
  2. TYPE:回傳 XML 型別,避免實體字元編碼(< > &)
  3. .value('.', 'NVARCHAR(MAX)'):提取 XML 純文字
  4. STUFF(..., 1, 1, ''):移除首字元多餘逗號

方案二:LEFT + LEN 去首逗號(較直觀)

SELECT 
    CityName,
    LEFT(UserList, LEN(UserList) - 1) AS Users
FROM (
    SELECT 
        CityName,
        (SELECT UserName + ',' 
         FROM @T1 
         WHERE CityName = t.CityName 
         FOR XML PATH(''), TYPE
        ).value('.', 'NVARCHAR(MAX)') AS UserList
    FROM @T1 t
    GROUP BY CityName
) d;

方案三:STRING_AGG (SQL Server 2017+ / Azure SQL)

SELECT 
    CityName,
    STRING_AGG(UserName, ',') WITHIN GROUP (ORDER BY UserName) AS Users
FROM @T1
GROUP BY CityName;
  • 原生函數,語法簡潔、效能最優、支援 WITHIN GROUP (ORDER BY ...) 排序
  • 可搭配 DISTINCT:STRING_AGG(DISTINCT UserName, ',')

效能對比(百萬行測試)

方案CPU 時間邏輯讀取備註
STUFF + FOR XML PATH~1200ms~45,000XML 解析開銷大
LEFT + LEN + FOR XML PATH~1150ms~44,000相近
STRING_AGG~300ms~12,000原生實作,無 XML 開銷

進階場景

多欄位合併

STRING_AGG(CONCAT(UserName, ':', Email), ';')  -- 2017+
-- 或 XML 方案
STUFF((SELECT ';' + UserName + ':' + Email FROM ... FOR XML PATH(''), TYPE).value('.','NVARCHAR(MAX)'),1,1,'')

條件過濾合併

STRING_AGG(CASE WHEN Active = 1 THEN UserName END, ',')  -- 僅合併啟用用戶

超長字串截斷處理

-- 限制長度 4000 字元
LEFT(STRING_AGG(UserName, ','), 4000) + CASE WHEN LEN(STRING_AGG(UserName, ',')) > 4000 THEN '...' ELSE '' END

避坑指南

  • NULL 值:STRING_AGG 自動忽略 NULL;XML 方案需 ISNULL(UserName,'')
  • 排序:XML 方案子查詢加 ORDER BY;STRING_AGG 用 WITHIN GROUP
  • FOR XML PATH 實體編碼:資料含 <>& 時**必須**加 TYPE + .value(),否則會被編碼為 &lt; 等
  • 最大長度:NVARCHAR(MAX) 2GB,但應用層可能有限制(如 SSRS 4000 字元)

SQL Server 2017+ 環境**首選 STRING_AGG**;舊版本用 STUFF + FOR XML PATH('', TYPE).value() 經典模式。理解 XML 方案原理,有助於維護存量代碼與跨版本相容。

copyright © 萬盛學電腦網 all rights reserved