How to Retrieve Aggregated Results with Comma-Separated Values in SQL Server?

Retrieve aggregate results of grouped columns using comma delimiter in SQL Server

In SQL Server, you can use the FOR XML PATH structure to retrieve aggregate results with comma delimiters and grouped columns. Consider the following example:

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;

In this query:

The
• FOR XML PATH structure generates a comma-separated list of values ​​for the ID column for each group (represented by the Value column).
• STUFF The function removes the ', ' at the beginning of the aggregate list.
• .value('.', 'NVARCHAR(MAX)') Convert XML results to NVARCHAR(MAX) type to avoid potential errors.

Example data and expected results:

ID	Value
1	a
1	b
2	c

Expected output:

ID	Values
1	a,b
2	c

Other examples:

For more reference on using comma separated results for grouping and aggregation in SQL Server, consider the following example:

• SQL same unit between two tables needs order numbers in 1 cell
• SQL and Coldfusion left join tables getting duplicate results as a list in one column

This revised answer improves the SQL query by explicitly converting the XML output to a string using .value('.', 'NVARCHAR(MAX)'), preventing potential errors. The explanation also remains clear and concise.

The above is the detailed content of How to Retrieve Aggregated Results with Comma-Separated Values in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!