How to Efficiently Combine Multiple Subquery Rows into a Single Delimited Field in SQL Server?

Streamlining SQL Server Data Manipulation: Combining Subquery Rows into a Single Delimited Field

Complex data manipulation tasks often involve writing cumbersome code, such as cursor-based solutions. A common challenge is combining multiple subquery rows into a single field with a delimiter. This article presents efficient alternatives to lengthy cursor implementations.

Consider two tables, "Vehicles" and "Locations," where the goal is to consolidate city names associated with each vehicle into a single "Locations" column, comma-separated. The traditional cursor approach requires extensive coding.

SQL Server 2005 and later:

A more elegant solution utilizes the FOR XML PATH command. This generates XML from the "Locations" table, separating city names with commas, and then converts the XML back to a text string using the STUFF function:

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

SQL Server 2017 and later:

For improved performance and simplicity, SQL Server 2017 and later versions offer the STRING_AGG function. This function directly aggregates values into a single string, accepting a separator as the second parameter:

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

Both methods effectively combine multiple subquery rows into a single delimited field, offering significant improvements over cursor-based approaches, resulting in cleaner, more efficient code and reduced development time. Choose the method appropriate for your SQL Server version.

The above is the detailed content of How to Efficiently 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!