How to Concatenate Multiple Rows into a Single Comma-Separated String in SQL Server?

Efficiently Combining Multiple Rows into a Single Comma-Separated String in SQL Server

SQL Server often requires aggregating data from multiple rows into a single, comma-separated string, especially when grouping results. This is a common task for reporting and data presentation. A powerful method for achieving this utilizes the FOR XML PATH construct.

Let's examine a table with "ID" and "Value" columns:

ID	Value
1	a
1	b
2	c

The following SQL query efficiently concatenates Value entries for each unique ID into a comma-separated string:

<code class="language-sql">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;</code>

This query uses a subquery with FOR XML PATH('') to concatenate the values. The .value('.', 'NVARCHAR(MAX)') method converts the XML result into a string. STUFF removes the leading comma and space.

The output will be:

ID	Values
1	a, b
2	c

This approach provides a clean and efficient way to generate comma-separated aggregated values in SQL Server, enhancing the readability and usability of your data.

The above is the detailed content of How to Concatenate Multiple Rows into a Single Comma-Separated String in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!