How to Aggregate Comma-Separated Values Using SQL Server's GROUP BY Clause?

Using SQL Server's GROUP BY to Concatenate Comma-Separated Values

SQL Server's GROUP BY clause is invaluable for aggregating data based on shared attributes. However, combining multiple values into a single comma-separated string requires a slightly more advanced approach. This is where the STUFF function proves useful.

Here's how you can achieve comma-separated aggregation using GROUP BY and STUFF:

<code class="language-sql">SELECT ReportId, Email = 
    STUFF((SELECT ', ' + Email
           FROM your_table b 
           WHERE b.ReportId = a.ReportId 
          FOR XML PATH('')), 1, 2, '')
FROM your_table a
GROUP BY ReportId</code>

Let's dissect this query:

• Outer SELECT: This retrieves the ReportId and the aggregated Email string.
• STUFF function: This cleverly concatenates emails, inserting a comma and space between each.
• Inner SELECT: This subquery retrieves all emails associated with a specific ReportId.
• FOR XML PATH(''): This converts the inner query's result set into an XML string, providing a convenient format for concatenation.
• WHERE clause: This ensures the inner query only selects emails matching the ReportId from the outer query.

Applying this query to sample data would produce the following result:

ReportId	Email
1	[email protected], [email protected]
2	[email protected]
3	[email protected], [email protected]

This technique efficiently generates comma-separated lists, simplifying the handling of aggregated data.

The above is the detailed content of How to Aggregate Comma-Separated Values Using SQL Server's GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!