需求:按 xh 分組,將各組 name 以分隔符連接。如:0001 → 'Alice,Bob,Charlie'。本文對比三種主流實現方案。
CREATE TABLE t_table (xh VARCHAR(10), th INT, name VARCHAR(20));
INSERT INTO t_table VALUES
('0001',1,'Alice'),('0001',2,'Bob'),('0001',3,'Charlie'),
('0002',1,'David'),('0002',2,'Eve');
SELECT xh,
STUFF((
SELECT ',' + name
FROM t_table t2
WHERE t2.xh = t1.xh
ORDER BY th
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '') AS ConcatNames
FROM t_table t1
GROUP BY xh;
解析: 子查詢產生 ,Alice,Bob,Charlie XML 片段,.value('.', 'NVARCHAR(MAX)') 取文本,STUFF(...,1,1,'') 移除首個逗號。TYPE 指令避免特殊字元實體化 (< > &)。
SELECT xh, STRING_AGG(name, ',') WITHIN GROUP (ORDER BY th) AS ConcatNames FROM t_table GROUP BY xh;
WITHIN GROUP (ORDER BY ...) 保證順序。若受限於 SQL Server 2005/2008 R2 且 FOR XML 效能不足:
IBinarySerialize、SqlUserDefinedAggregate。PERMISSION_SET = SAFE。CREATE ASSEMBLY StringConcat FROM '...dll' WITH PERMISSION_SET = SAFE;CREATE AGGREGATE dbo.StringConcat (@input NVARCHAR(MAX), @delimiter NVARCHAR(10)) RETURNS NVARCHAR(MAX) EXTERNAL NAME StringConcat.[Namespace.ClassName];SELECT xh, dbo.StringConcat(name, ',') FROM t_table GROUP BY xh;優勢: 流式處理,記憶體佔用恆定,極大資料集不會 OOM。劣勢: 部署複雜、需 CLR 啟用、版本升級維護成本高。
| 方法 | 執行時間 | CPU 時間 | 記憶體授予 |
|---|---|---|---|
| FOR XML PATH | 12.4 秒 | 11.8 秒 | 240 MB |
| STRING_AGG | 1.2 秒 | 1.1 秒 | 45 MB |
| CLR UDA | 2.8 秒 | 2.5 秒 | 60 MB |
TYPE 與 .value() 防特殊字元轉義。-- 僅聚合特定狀態 SELECT xh, STRING_AGG(CASE WHEN status = 'Active' THEN name END, ',') WITHIN GROUP (ORDER BY th) FROM t_table GROUP BY xh;