最佳的字符串连接/聚合方式

Question

我正在寻找一种方法，将不同行的字符串聚合成一行。我希望在许多不同的地方都能实现这个功能，所以有一个函数来方便这个操作会很好。我已经尝试过使用COALESCE和FOR XML的解决方案，但对我来说它们并不完全满意。

字符串聚合将会做类似于这样的操作：

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

我已经看了一下作为替代COALESCE和FOR XML的CLR-defined aggregate functions，但显然SQL Azure不支持CLR-defined的东西，这对我来说很痛苦，因为我知道如果能使用它，我将能解决很多问题。

有没有可能的解决方法，或者类似的最佳方法（可能不像CLR那样最佳，但是嘿，我会尽力而为），我可以用来聚合我的东西？

- matt

---

它确实能够运行，但我查看了执行计划，发现每个 for xml 在查询性能方面都占用了25%（即查询中的大部分内容！）。 - matt

有不同的方法来执行 for xml path 查询。有些比其他的更快。这可能取决于您的数据，但在我的经验中，使用 distinct 的查询比使用 group by 更慢。如果您正在使用 .value('.', nvarchar(max)) 来获取连接的值，则应将其更改为 .value('./text()[1]', nvarchar(max))。 - Mikael Eriksson

你接受的答案与我在http://stackoverflow.com/questions/11137075/which-is-the-best-way-to-form-the-string-value-using-column-from-a-table-with-ro 上的回答类似，我认为我的方法比XML更快。不要被查询成本所迷惑，你需要足够多的数据来确定哪个更快。XML更快，这也是@MikaelEriksson在同一问题上的答案。选择XML方法。 - Michael Buen

请在此处为本地化解决方案投票：https://connect.microsoft.com/SQLServer/feedback/details/1026336 - JohnLBevan

这里有一个有用的性能分析：https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation顺便说一句，如果您的数据包含表情符号或代理字符，XML方法会出现问题，除非您跳过一些步骤。 - devinbost

Answer 1

解决方案

“最优”的定义可能不同，但以下是使用常规Transact SQL连接不同行中的字符串的方法，这应该在Azure中正常工作。

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM dbo.SourceTable
),
Concatenated AS
(
    SELECT 
        ID, 
        CAST(Name AS nvarchar) AS FullName, 
        Name, 
        NameNumber, 
        NameCount 
    FROM Partitioned 
    WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, 
        CAST(C.FullName + ', ' + P.Name AS nvarchar), 
        P.Name, 
        P.NameNumber, 
        P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C 
                ON P.ID = C.ID 
                AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

解释

这种方法归结为三个步骤：

1. 使用OVER和PARTITION分组对行进行编号，并根据需要对其进行排序以进行连接。结果是一个带有Partitioned CTE的计数器，我们保留每个分区中的行计数以稍后过滤结果。

2. 使用递归CTE (Concatenated) 遍历行号(NameNumber列)，将 Name值添加到 FullName 列中。

3. 过滤掉除最高NameNumber之外的所有结果。

请记住，为了使此查询可预测，必须定义分组 (例如，在您的情况下，具有相同ID的行被连接) 和排序 (我假设在连接之前简单地按字母表顺序对字符串进行排序)。

我已经在SQL Server 2012上快速测试了以下数据的解决方案：

INSERT dbo.SourceTable (ID, Name)
VALUES 
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

查询结果：

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

- Serge Belov

---

我检查了这种方法与xmlpath的时间消耗，结果显示大约是4毫秒对比大约54毫秒。因此，在处理大规模情况下，xmlpath更好。我将在单独的回答中编写比较代码。 - QMaster

令人惊讶的是，CTE 对我来说速度要慢得多。https://sqlperformance.com/2014/08/t-sql-queries/sql-server-grouped-concatenation 比较了一堆技术，并且似乎也认同我的结果。 - Nickolay

这个针对超过100万条记录的表的解决方案不可行。 此外，我们在递归深度上有限制。 - Ardalan Shahgholi

@ArdalanShahgholi，您可能需要限制要操作的数据范围。否则，您将需要使用XML表单进行连接。递归CTE是分层评估，因此在执行最终限制之前，1M记录操作将导致大约2M记录的结果。 - GoldBishop

Azure中的MSSQL现在具有string_agg函数。 - nurettin

Answer 2

使用类似以下的FOR XML PATH方法真的很慢吗？Itzik Ben-Gan在他的T-SQL Querying书中写道，这种方法具有良好的性能（在我看来，Ben-Gan先生是一个可信赖的来源）。

create table #t (id int, name varchar(20))

insert into #t
values (1, 'Matt'), (1, 'Rocks'), (2, 'Stylus')

select  id
        ,Names = stuff((select ', ' + name as [text()]
        from #t xt
        where xt.id = t.id
        for xml path('')), 1, 2, '')
from #t t
group by id

- slachterman

---

当表的大小成为问题时，不要忘记在id列上放置索引。 - milivojeviCH

阅读了有关“stuff/for xml path”如何工作的文章（https://dev59.com/pl0Z5IYBdhLWcg3wphsE#31212160）后，我相信这是一个好的解决方案，尽管它的名字带有XML。 - Nickolay

@slackterman 这取决于要操作的记录数量。我认为在低计数方面，与CTE相比，XML存在缺陷，但在大量计数方面，它可以减轻递归部门的限制，并且如果正确而简洁地完成，则更易于导航。 - GoldBishop

这段代码会导致XML编码的文本（&被转换为&等）。更正确的for xml解决方案在这里提供。 - Frédéric

你能用JSON做到这个吗？ - TheEsnSiavashi

Answer 3

STRING_AGG()用于SQL Server 2017、Azure SQL和PostgreSQL中： https://www.postgresql.org/docs/current/static/functions-aggregate.html
https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql

GROUP_CONCAT()用于MySQL中：
http://dev.mysql.com/doc/refman/5.7/en/group-by-functions.html#function_group-concat

(感谢 @Brianjorden 和 @milanio 提供Azure的更新)

示例代码：

select Id
, STRING_AGG(Name, ', ') Names 
from Demo
group by Id

SQL Fiddle：http://sqlfiddle.com/#!18/89251/1

- Hrobky

---

我刚刚测试了一下，现在它在Azure SQL数据库中可以正常工作。 - milanio

STRING_AGG 被推迟到了 2017 年，它在 2016 年不可用。 - Morgan Thrapp

Answer 4

虽然 @serge 的回答是正确的，但我比较了他的方法与 xmlpath 的时间消耗，发现 xmlpath 更快。我会编写对比代码，你可以自行检查。

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (ID int, Name nvarchar(50))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE()

;WITH Partitioned AS
(
    SELECT 
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM @YourTable
),
Concatenated AS
(
    SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1
)
SELECT 
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 54 milliseconds

这是使用xmlpath的方法：

DECLARE @startTime datetime2;
DECLARE @endTime datetime2;
DECLARE @counter INT;
SET @counter = 1;

set nocount on;

declare @YourTable table (RowID int, HeaderValue int, ChildValue varchar(5))

WHILE @counter < 1000
BEGIN
    insert into @YourTable VALUES (@counter, ROUND(@counter/10,0), CONVERT(NVARCHAR(50), @counter) + 'CC')
    SET @counter = @counter + 1;
END

SET @startTime = GETDATE();

set nocount off
SELECT
    t1.HeaderValue
        ,STUFF(
                   (SELECT
                        ', ' + t2.ChildValue
                        FROM @YourTable t2
                        WHERE t1.HeaderValue=t2.HeaderValue
                        ORDER BY t2.ChildValue
                        FOR XML PATH(''), TYPE
                   ).value('.','varchar(max)')
                   ,1,2, ''
              ) AS ChildValues
    FROM @YourTable t1
    GROUP BY t1.HeaderValue

SET @endTime = GETDATE();

SELECT DATEDIFF(millisecond,@startTime, @endTime)
--Take about 4 milliseconds

- QMaster

---

+1，你这个QMaster（黑暗艺术大师）！我得到了一个更加惊人的差异。（在Windows Server 2008 R2上的SQL Server 2008 R2上，在Intel Xeon E5-2630 v4 @2.20 GHZ x2 w/ ~1 GB free上，约3000毫秒CTE vs.约70毫秒XML）。唯一的建议是：1）使用OP的或（最好是）通用术语来表示两个版本，2）由于OP的问题是如何“连接/聚合字符串”，这仅适用于字符串（而不是数字值），通用术语太过通用。只需使用“GroupNumber”和“StringValue”，3）声明并使用“Delimiter”变量，并使用“Len（Delimiter）”而不是“2”。 - Tom

+1，不要将特殊字符扩展为XML编码（例如，“＆”不会像其他低劣解决方案中那样扩展为“＆amp;”） - Reversed Engineer

我必须说... 得分！在我的测试中，这将查询所需的时间从2.3秒减少到25毫秒（从大约80K个源行返回2600行...）谢谢！SQL Server 2016，企业版... - Traderhut Games

Answer 5

更新：Ms SQL Server 2017+，Azure SQL 数据库

您可以使用：STRING_AGG。

对于 OP 的请求，使用方法非常简单：

SELECT id, STRING_AGG(name, ', ') AS names
FROM some_table
GROUP BY id

阅读更多

我的旧的无意义的回答被正确地删除了（下面保持不变），但如果将来有人碰巧着陆在这里，有好消息。他们也在Azure SQL数据库中实现了STRING_AGG()。这应该提供了原始请求中所要求的精确功能，具有本地和内置支持。@hrobky曾在当时提到过这个作为SQL Server 2016的一个特性。

---旧帖子： 这里的声望不够高，不能直接回复@hrobky，但STRING_AGG看起来很棒，但目前仅适用于SQL Server 2016 vNext。希望它很快也会出现在Azure SQL数据库中..

- Brian Jorden

---

我刚刚测试了一下，在Azure SQL数据库中它的表现非常出色。 - milanio

STRING_AGG()函数被称为在SQL Server 2017中可用，适用于任何兼容级别。https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql - user

是的。STRING_AGG在SQL Server 2016中不可用。 - Magne

Answer 6

您可以使用+=来连接字符串，例如：

declare @test nvarchar(max)
set @test = ''
select @test += name from names

如果您选择@test，则会给出所有名称的连接

- jvc

---

请指定SQL方言或版本，以确定其受支持的时间。 - Hrobky

这在SQL Server 2012中有效。 请注意，逗号分隔的列表可以使用select @test += name + ', ' from names创建。 - Art Schmidt

这里使用了未定义行为，不太安全。如果查询中有ORDER BY语句，这可能会导致奇怪/不正确的结果。应该使用列出的替代方法之一。 - Dan Oberlam

这种类型的查询从未被定义为行为，而在SQL Server 2019中，我们发现它的行为不正确比之前的版本更加一致。不要使用这种方法。 - Matthew Rodatus

Answer 7

我认为Serge的答案非常有前途，但我也遇到了性能问题。然而，当我重构它以使用临时表并不包括双CTE表时，性能从1分40秒提高到了1000个组合记录的次秒级别。以下是该代码，供所有需要在旧版SQL Server上执行此操作而无需使用FOR XML的人使用：

DECLARE @STRUCTURED_VALUES TABLE (
     ID                 INT
    ,VALUE              VARCHAR(MAX) NULL
    ,VALUENUMBER        BIGINT
    ,VALUECOUNT         INT
);

INSERT INTO @STRUCTURED_VALUES
SELECT   ID
        ,VALUE
        ,ROW_NUMBER() OVER (PARTITION BY ID ORDER BY VALUE) AS VALUENUMBER
        ,COUNT(*) OVER (PARTITION BY ID)    AS VALUECOUNT
FROM    RAW_VALUES_TABLE;

WITH CTE AS (
    SELECT   SV.ID
            ,SV.VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    WHERE   VALUENUMBER = 1

    UNION ALL

    SELECT   SV.ID
            ,CTE.VALUE + ' ' + SV.VALUE AS VALUE
            ,SV.VALUENUMBER
            ,SV.VALUECOUNT
    FROM    @STRUCTURED_VALUES SV
    JOIN    CTE 
        ON  SV.ID = CTE.ID
        AND SV.VALUENUMBER = CTE.VALUENUMBER + 1

)
SELECT   ID
        ,VALUE
FROM    CTE
WHERE   VALUENUMBER = VALUECOUNT
ORDER BY ID
;

- Tom Halladay

Answer 8

试试这个，我在我的项目中使用它

DECLARE @MetricsList NVARCHAR(MAX);

SELECT @MetricsList = COALESCE(@MetricsList + '|', '') + QMetricName
FROM #Questions;

- Vladyslav Fomin

---

好的...那么发布代码来做多个Rowset，这正是OP所需的。请注意...如果它是标量或mTVF函数，请忘记它。:D - Jeff Moden