How Can I Concatenate Multiple Values into a Comma-Separated String Using GROUP BY in SQL Server?

Aggregating Data with Comma-Separated Strings in SQL Server

SQL Server's GROUP BY clause is excellent for data aggregation. But how do you combine multiple values from grouped rows into a single, comma-separated string?

The Challenge:

Imagine a table like this:

<code>ID    ReportId     Email
1     1            a@domain.com
2     2            b@domain.com
3     1            c@domain.com
4     3            d@domain.com
5     3            e@domain.com</code>

The goal is to group by ReportId and concatenate the emails into a comma-separated list for each report.

Solution: Subquery and FOR XML PATH

Here's how to achieve this using a subquery and the FOR XML PATH method:

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

Explanation:

• Subquery: The inner SELECT statement retrieves all emails associated with a specific ReportId.
• FOR XML PATH(''): This function converts the email results into an XML string, with each email as a separate node. The '' ensures no parent node is created.
• STUFF: The STUFF function removes the leading comma and space added by the SELECT statement, resulting in a clean comma-separated string.

Result:

The query will produce the following output:

<code>ReportId     Email
1            a@domain.com, c@domain.com
2            b@domain.com
3            d@domain.com, e@domain.com</code>

This effectively consolidates multiple email addresses into a single comma-separated string for each ReportId. While this approach works well, consider exploring other database-specific functions like STRING_AGG (available in later SQL Server versions) for potentially more efficient solutions.

The above is the detailed content of How Can I Concatenate Multiple Values into a Comma-Separated String Using GROUP BY in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!