FOR XML 関数や CLR 関数を使用せずに SQL Server の複数の行の文字列を効率的に連結するにはどうすればよいですか?

SQL Server の文字列連結メソッド

問題の説明:

複数行の文字列を 1 行に集約する必要があります。 COALESCE と FOR XML は必要なことを実行しません。また、Azure は CLR 定義の集計関数をサポートしていません。最適な代替案を探す必要があります。

解決策:

標準の Transact SQL を使用すると、次の手順に従って文字列を効率的に連結できます。

1. 番号付き行: グループ化基準に基づいてデータをパーティション化し、各パーティション内に行番号を割り当てます。
2. 再帰的結合: 行番号に基づいて結合文字列を反復する再帰的 CTE を作成します。
3. 結果のフィルター: 行番号が最も大きい結果のみが選択され、完全な接続が保証されます。

手順:

この方法では 3 つの CTE が使用されます:

• パーティション: 各パーティション内の行に行番号を割り当てます。
• 連結: 連結された文字列を再帰的に構築します。
• 最終結果: 連結された行をフィルターして、完全な結果のみを含めます。

このソリューションを使用するには、グループ化と並べ替えの基準を指定する必要があります。たとえば、あなたの場合、同じ ID を持つ行が連結され、アルファベット順に並べ替えられます。

例:

次のデータについて考えてみましょう:

<code>ID  Name
-- --
1   Matt
1   Rocks
2   Stylus
3   Foo
3   Bar
3   Baz</code>

クエリとその出力は次のとおりです:

<code class="language-sql">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(max)) AS FullName, 
        Name, 
        NameNumber, 
        NameCount 
    FROM Partitioned 
    WHERE NameNumber = 1

    UNION ALL

    SELECT 
        P.ID, 
        CAST(C.FullName + ', ' + P.Name AS nvarchar(max)), 
        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;</code>

<code>ID  FullName
-- ------------------------------
2   Stylus
3   Bar, Baz, Foo
1   Matt, Rocks</code>

改善の説明: より長い文字列連結結果をサポートするために、nvarchar タイプを nvarchar(max) に変更します。 これにより、潜在的な文字列長オーバーフロー エラーが回避されます。

以上がFOR XML 関数や CLR 関数を使用せずに SQL Server の複数の行の文字列を効率的に連結するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。