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

SOLUTION

optimal的定义可能有所不同，但下面介绍如何使用常规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

EXPLANATION

该方法可归结为三个步骤:

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