How Can I Efficiently Concatenate Rows from a Subquery in SQL Server?

SQL Server Row Concatenation: A Custom Function and a Modern Approach

This article demonstrates two methods for efficiently concatenating rows from a subquery within SQL Server, resulting in a single delimited string. The first uses a custom function, offering compatibility across various SQL Server versions. The second leverages the STRING_AGG function, available in SQL Server 2017 and later, providing enhanced performance.

Method 1: The JoinRows User-Defined Function

For broader SQL Server version compatibility, a custom function provides a clean solution. The JoinRows function below takes a separator character and a table as input, returning a concatenated string:

CREATE FUNCTION JoinRows (
    @Separator CHAR(1),
    @InputTable TABLE (ID INT, Value VARCHAR(MAX))
)
RETURNS VARCHAR(MAX)
BEGIN
    RETURN (
        SELECT COALESCE(@Separator + I.Value, I.Value)
        FROM @InputTable AS I
        FOR XML PATH('')
    );
END;

This function can be integrated into your query as follows:

SELECT
    VehicleID,
    Name,
    JoinRows(', ', (SELECT City FROM Location WHERE VehicleID = Vehicles.VehicleID)) AS Locations
FROM Vehicles;

Method 2: STRING_AGG (SQL Server 2017 and later)

For SQL Server 2017 and subsequent versions, the built-in STRING_AGG function offers a more streamlined and often faster approach:

SELECT
    VehicleID,
    Name,
    STRING_AGG(City, ', ') WITHIN GROUP (ORDER BY City) AS Locations
FROM Vehicles
INNER JOIN Location ON Vehicles.VehicleID = Location.VehicleID
GROUP BY VehicleID, Name;

This method directly joins the Vehicles and Location tables and uses STRING_AGG to aggregate the City values, partitioned by VehicleID, providing a more efficient solution for newer SQL Server instances. Note the addition of WITHIN GROUP (ORDER BY City) for predictable ordering of concatenated cities. This is optional but recommended for consistent results. The GROUP BY clause is also necessary to ensure correct aggregation.

Choose the method that best suits your SQL Server version and performance requirements. For older versions, the custom function is necessary; for newer versions, STRING_AGG offers a significant performance advantage.

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