How Can SQL Server's STRING_AGG Function Be Used for Grouped String Aggregation?

Grouped String Aggregation: LISTAGG Function in SQL Server

String aggregation in SQL Server provides a way to combine multiple string values into a single concatenated string. When working with grouped data, this functionality is crucial in scenarios like generating comma-separated lists of distinct values.

Consider the following schema, where we want to aggregate car models within each car make:

| CarMakeID | CarMake
------------------------
| 1 | SuperCars
| 2 | MehCars

| CarMakeID | CarModelID | CarModel
-----------------------------------------
| 1 | 1 | Zoom
| 2 | 1 | Wow
| 3 | 1 | Awesome
| 4 | 2 | Mediocrity
| 5 | 2 | YoureSettling

Our desired output is:

| CarMakeID | CarMake | CarModels
---------------------------------------------
| 1 | SuperCars | Zoom, Wow, Awesome
| 2 | MehCars | Mediocrity, YoureSettling

To achieve this aggregation, SQL Server provides several options. However, the recommended approach for string concatenation is to utilize the STRING_AGG function.

The following revised query uses the STRING_AGG function in place of AGG from the initial query:

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

The STRING_AGG function is a built-in aggregate function that takes a column value and concatenates all distinct values together into a single string. The optional SEPARATOR parameter allows us to specify the delimiter between values, and in this case, we have chosen a comma.

This query will produce the desired output, where the CarModels column contains a comma-separated list of car models for each make.

The above is the detailed content of How Can SQL Server's STRING_AGG Function Be Used for Grouped String Aggregation?. For more information, please follow other related articles on the PHP Chinese website!