sql - 连接/聚合字符串的最佳方法

Question

我正在寻找一种将不同行的字符串聚合到一行中的方法。我希望在许多不同的地方做到这一点，所以有一个函数来促进这一点会很好。我尝试过使用 COALESCE 和 FOR XML 的解决方案，但它们并不适合我。

字符串聚合会做这样的事情:

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

我查看了CLR-defined aggregate functions作为 COALESCE 和 FOR XML 的替代品，但显然 SQL Azure 不支持 CLR 定义的东西，这对我来说很痛苦，因为我知道使用它可以解决我的很多问题。

是否有任何可能的解决方法或类似的最佳方法(可能不如 CLR 最佳，但是嘿我会采取我能得到的)来聚合我的东西？

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对行进行编号，并根据连接需要对它们进行排序。结果是分区 CTE。我们保留每个分区中的行数，以便稍后过滤结果。

2. 使用递归 CTE(串联)迭代行号(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