SQL Server で複数のサブクエリ行を 1 つの区切り文字で区切られたフィールドに結合するにはどうすればよいですか?

SQL Server: 複数のサブクエリ行を単一の区切りフィールドに結合する

この記事では、サブクエリの行を SQL Server 内の 1 つの区切り文字で区切られたフィールドに連結するためのいくつかの方法について説明します。 さまざまな SQL Server バージョンに適した手法を検討します。

方法 1: XML PATH の場合 (SQL Server 2005 以降)

この古典的なアプローチでは、FOR XML PATH を使用して結果を効率的に連結します。

<code class="language-sql">SELECT 
    [VehicleID],
    [Name],
    STUFF((SELECT ', ' + [City]
           FROM [Location]
           WHERE (VehicleID = Vehicle.VehicleID)
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Locations
FROM [Vehicle]</code>

方法 2: STRING_AGG (SQL Server 2017 以降)

新しい SQL Server バージョンの場合、STRING_AGG は、より読みやすく、多くの場合高速なソリューションを提供します。

<code class="language-sql">SELECT  
    [VehicleID],
    [Name],
    STRING_AGG([City], ', ') AS Locations
FROM   
    [Vehicle] V
JOIN
    [Location] L ON V.VehicleID = L.VehicleID
GROUP BY
    [VehicleID], [Name]</code>

この特定のケースでは、サブクエリと比較してパフォーマンスを向上させるために、ここで JOIN を使用することに注意してください。

方法 3: カスタム関数 (すべての SQL Server バージョン)

さまざまな SQL Server バージョンおよびシナリオ間で最大限の再利用性を実現するために、カスタム関数により柔軟性が提供されます。

<code class="language-sql">CREATE FUNCTION dbo.JoinRows (@Subquery NVARCHAR(MAX), @Delimiter NVARCHAR(1))
RETURNS NVARCHAR(MAX)
AS
BEGIN
    DECLARE @Result NVARCHAR(MAX) = '';
    DECLARE @Row NVARCHAR(MAX);
    DECLARE @NextRow CURSOR;

    SET @NextRow = CURSOR FOR SELECT [Value] FROM OPENROWSET('SQLNCLI', 'Server=(local);Trusted_Connection=yes;', @Subquery);

    OPEN @NextRow;
    FETCH NEXT FROM @NextRow INTO @Row;

    WHILE @@FETCH_STATUS = 0
    BEGIN
        SET @Result += @Row + @Delimiter;
        FETCH NEXT FROM @NextRow INTO @Row;
    END;

    CLOSE @NextRow;
    DEALLOCATE @NextRow;

    RETURN STUFF(@Result, LEN(@Result), LEN(@Delimiter), ''); -- Remove trailing delimiter
END;
GO</code>

この関数は OPENROWSET を使用して動的サブクエリを実行します。 これは動的 SQL を直接実行するより安全です。 この関数は次のように使用できます:

<code class="language-sql">SELECT 
    VehicleID, 
    Name, 
    dbo.JoinRows((SELECT City FROM Location WHERE VehicleID = Vehicle.VehicleID), ',') AS Locations
FROM Vehicle;</code>

SQL Server のバージョンとパフォーマンス要件に最も適した方法を選択してください。 SQL Server 2017 以降の場合、一般に、そのシンプルさとパフォーマンスの点で STRING_AGG が推奨されます。 古いバージョンまたはより複雑なシナリオの場合、カスタム関数は堅牢で再利用可能なソリューションを提供します。

以上がSQL Server で複数のサブクエリ行を 1 つの区切り文字で区切られたフィールドに結合するにはどうすればよいですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。