How Can I Achieve String Aggregation in SQL Server Like Oracle's LISTAGG Function?

Grouped String Aggregation in SQL Server: Finding a LISTAGG Alternative

In SQL Server, string aggregation can be a challenge. Given a table structure that lists car makes and models, you may want to produce a dataset that groups car models by make and concatenates their values into a single column, similar to the LISTAGG function in Oracle.

To achieve this in SQL Server, you can utilize the FOR XML PATH method. Consider the following query:

SELECT CarMakeID,
       CarMake,
       (SELECT ModelNames.ModelNames
        FROM (SELECT DISTINCT CarModel AS ModelNames
              FROM CarModels
              WHERE CarMakeID = make.CarMakeID
              FOR XML PATH('')
             ) AS ModelNames
       ) AS CarModels
FROM CarMakes make

The subquery (SELECT ModelNames.ModelNames... concatenates the distinct model names for each car make using the FOR XML PATH('') method. This generates a comma-separated list of models that is then assigned to the CarModels column in the outer query.

By replacing AGG(CarModel) in your original query with the subquery described above, you can achieve the desired grouping and string aggregation in SQL Server.

The above is the detailed content of How Can I Achieve String Aggregation in SQL Server Like Oracle's LISTAGG Function?. For more information, please follow other related articles on the PHP Chinese website!