[解決済み] 文字列の連結/集約の最適な方法

どのように解決するのですか？

解決方法

の定義は 最適 の定義は様々ですが、ここでは通常の 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

説明

アプローチは3つのステップに集約されます。

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