How to Combine Multiple SQL Rows into a Single Delimited Field?

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

Efficiently merging multiple rows from a subquery into a single, delimited field within SQL Server requires a robust approach. This article presents two effective techniques, suitable for different SQL Server versions.

FOR XML PATH (SQL Server 2005 and later):

This method, compatible with SQL Server 2005 and subsequent versions, leverages the FOR XML PATH command for string concatenation. Here's an example:

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

This query uses FOR XML PATH to build a comma-separated string of city values for each VehicleID. The STUFF function removes the leading comma.

STRING_AGG (SQL Server 2017 and later):

For SQL Server 2017 and later, the STRING_AGG function offers a cleaner and more efficient solution:

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

STRING_AGG directly concatenates the City values with a specified separator (a comma and space in this case). This approach is generally preferred for its improved readability and performance.

Both methods effectively aggregate data from multiple rows into a single delimited field, simplifying data manipulation within SQL Server. Choose the method best suited to your SQL Server version for optimal results.

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