☰

文字列を連結/集約する最適な方法 質問する

tag-icon • tag-icon sql sql-server aggregate-functions string-concatenation
文字列を連結/集約する最適な方法 質問する

異なる行の文字列を 1 つの行に集約する方法を探しています。 これをさまざまな場所で実行したいので、これを容易にする関数があると便利です。COALESCEとを使用したソリューションを試しましたFOR XMLが、私には十分ではありませんでした。

文字列の集約は次のようになります。

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

私は見てみましたCLR定義の集計関数COALESCEとの代わりとしてFOR XML、しかしどうやらSQLアズール ではないCLR 定義のものをサポートしますが、これを使用できれば多くの問題が解決されることがわかっているので、私にとっては面倒です。

回避策や、同様に最適な方法（CLRほど最適ではないかもしれませんが、おい自分のものを集約するために使用できるもの（入手できるものは何でも）はありますか？

ベストアンサー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

説明

このアプローチは、次の 3 つのステップに要約されます。

  1. 連結の必要に応じて、行に番号を付けOVER、PARTITIONグループ化し、順序付けします。結果はPartitionedCTE です。後で結果をフィルター処理するために、各パーティションの行数を保持します。

  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

おすすめ記事