How to Perform Grouped String Aggregation in SQL Server Without LISTAGG?

Grouped String Aggregation in SQL Server

When working with SQL Server, one common task is to aggregate string values from multiple rows into a single, comma-separated string. For instance, given a table with car manufacturers and models, you might want to create a dataset that groups car models by car make.

Alternative to LISTAGG for SQL Server

In Oracle, this task is typically accomplished using the LISTAGG function. However, SQL Server does not have a built-in LISTAGG equivalent. Instead, you can use a combination of the STUFF and FOR XML PATH functions to achieve the same result.

The following query demonstrates how to achieve grouped string aggregation in SQL Server using STUFF and FOR XML PATH:

SELECT
  make.CarMakeID,
  make.CarMake,
  (
    SELECT
      STUFF(
        (
          SELECT
            ',' + model.CarModel
          FROM CarModels
          WHERE
            model.CarMakeID = make.CarMakeID
          FOR XML PATH('')
        ),
        1,
        1,
        ''
      ) AS CarModels
  )
FROM CarMakes AS make;

In this query, the subquery uses the FOR XML PATH function to concatenate the car models into a single XML string, separated by commas. The STUFF function then removes the leading comma and returns the concatenated string as the CarModels column.

The following is the output of the query:

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

This technique provides a straightforward and efficient way to achieve grouped string aggregation in SQL Server, even in situations where string concatenation is complex or includes special characters.

The above is the detailed content of How to Perform Grouped String Aggregation in SQL Server Without LISTAGG?. For more information, please follow other related articles on the PHP Chinese website!