How to Aggregate and Comma-Separate Values in SQL Server Using FOR XML PATH?

SQL Server: Concatenating Values with Commas during Aggregation

Often in SQL Server, you need to group data by a specific column and then combine related values into a single comma-separated string. Let's illustrate this with an example. Imagine a table named YourTable with columns ID and Value:

<code>ID   |  Value
-------|--------
1    |   a
1    |   b
2    |   c</code>

The goal is to generate a result set where each unique ID has a corresponding comma-separated string of its associated Value entries. We can achieve this using the FOR XML PATH method:

SELECT 
    ID, 
    STUFF((SELECT ', ' + Value
           FROM YourTable t2
           WHERE t1.ID = t2.ID
           FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS Values
FROM YourTable t1
GROUP BY ID;

This query works as follows:

1. Outer SELECT: This selects the ID column and prepares for aggregation.
2. Inner SELECT: This retrieves all Value entries matching the current ID from the outer query. The ', ' Value adds a comma and space before each value.
3. FOR XML PATH(''): This converts the inner query's result into an XML string, effectively concatenating the values. The empty string '' prevents XML tags from being generated.
4. .value('.', 'NVARCHAR(MAX)'): This extracts the concatenated string from the XML.
5. STUFF(..., 1, 2, ''): This removes the leading ', ' from the concatenated string.

The final output will be:

<code>ID   |  Values
-------|--------
1    |   a, b
2    |   c</code>

This technique provides a concise and efficient way to perform comma-separated aggregation in SQL Server.

The above is the detailed content of How to Aggregate and Comma-Separate Values in SQL Server Using FOR XML PATH?. For more information, please follow other related articles on the PHP Chinese website!