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

Efficiently Combining Subquery Results into a Single Delimited Field in SQL Server

SQL Server offers several methods for concatenating multiple rows from a subquery into a single field with a delimiter. While server-side cursors are an option, they're often less efficient. This article explores more effective techniques.

FOR XML PATH: A Solution for Older SQL Server Versions (2005 and Below)

For SQL Server 2005 and earlier versions, the FOR XML PATH command provides a concise solution:

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

This cleverly uses XML path generation to concatenate City values, then STUFF removes the initial comma.

STRING_AGG: The Preferred Method for SQL Server 2017 and Later

SQL Server 2017 and later versions introduce STRING_AGG, a significantly improved function:

<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 offers better performance and allows for custom delimiters, making it the recommended approach for modern SQL Server deployments.

Summary

These methods provide efficient ways to consolidate multiple subquery rows into a single delimited string within SQL Server, streamlining data manipulation tasks. Choosing the appropriate method depends on your SQL Server version; STRING_AGG is the preferred choice for newer versions due to its enhanced performance and flexibility.

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