How Can I Efficiently Concatenate Row Values in SQL Server Without LISTAGG?

Concatenating Row Values in SQL Server: An Alternative to LISTAGG

One of the common challenges in SQL is aggregating string values across rows. In this context, LISTAGG is a convenient function supported by many database systems. However, in SQL Server, there is no direct equivalent to LISTAGG.

To achieve similar functionality in SQL Server, one approach is to utilize a subquery within the main query. However, the traditional approach shown below has a potential drawback:

SELECT *, 
 (SELECT AGG(CarModel) 
  FROM CarModels model
  WHERE model.CarMakeID = make.CarMakeID
  GROUP BY make.CarMakeID) as CarMakes
FROM CarMakes make

In this query, AGG can be replaced with a combination of STRING_AGG and GROUP_CONCAT functions:

SELECT *, 
 (SELECT STRING_AGG(CarModel, ', ')
  FROM CarModels model
  WHERE model.CarMakeID = make.CarMakeID
  GROUP BY make.CarMakeID) as CarMakes
FROM CarMakes make

This modified query provides a robust solution for concatenating row values in SQL Server. However, it's important to note that the performance characteristics of this approach can vary depending on the size and complexity of the dataset.

The above is the detailed content of How Can I Efficiently Concatenate Row Values in SQL Server Without LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!