How Can I Combine Multiple Subquery Rows into a Single Delimited Field in SQL Server?

SQL Server: Combining Multiple Subquery Rows into a Single Delimited Field

This article explores several methods for concatenating rows from a subquery into a single delimited field within SQL Server. We'll examine techniques suitable for different SQL Server versions.

Method 1: FOR XML PATH (SQL Server 2005 and later)

This classic approach uses FOR XML PATH to efficiently concatenate results:

<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>

Method 2: STRING_AGG (SQL Server 2017 and later)

For newer SQL Server versions, STRING_AGG provides a more readable and often faster solution:

<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>

Note the use of a JOIN here for improved performance compared to a subquery in this specific case.

Method 3: Custom Function (All SQL Server Versions)

For maximum reusability across different SQL Server versions and scenarios, a custom function offers flexibility:

<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>

This function uses OPENROWSET to execute the dynamic subquery. This is safer than directly executing dynamic SQL. The function can then be used like this:

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

Choose the method that best suits your SQL Server version and performance requirements. For SQL Server 2017 and later, STRING_AGG is generally recommended for its simplicity and performance. For older versions, or for more complex scenarios, the custom function provides a robust and reusable solution.

The above is the detailed content of How Can I Combine Multiple Subquery Rows into a Single Delimited Field in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!