How to Concatenate Emails by Report ID Using SQL Server's GROUP BY Clause?

SQL Server: Concatenating Emails within Report ID Groups Using GROUP BY

In SQL Server, efficiently grouping data and combining related values into single strings is a common task. The GROUP BY clause groups rows based on specified columns, enabling aggregate function use for data manipulation and calculations.

Combining Emails by Report ID

Imagine a table structured like this:

<code>ID    ReportId     Email
1     1            [email protected]
2     2            [email protected]
3     1            [email protected]
4     3            [email protected]
5     3            [email protected]</code>

The goal is to group rows by ReportId and concatenate corresponding emails into comma-separated strings. The desired output:

<code>ReportId     Email
1            [email protected], [email protected]
2            [email protected]
3            [email protected], [email protected]</code>

Leveraging the STUFF() Function

This is effectively achieved using the STUFF() function within a subquery:

<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>

Detailed Explanation:

• The inner subquery selects all emails for each ReportId, prepending each email with a comma and space.
• FOR XML PATH('') transforms the subquery results into a single XML string.
• STUFF() removes the leading comma and space from the XML string, resulting in the desired comma-separated email list.

Further Considerations:

• Other aggregate functions (e.g., SUM(), AVG()) can be used with GROUP BY for various calculations on grouped data.
• STUFF() is a highly versatile function for diverse string manipulation operations.

The above is the detailed content of How to Concatenate Emails by Report ID Using SQL Server's GROUP BY Clause?. For more information, please follow other related articles on the PHP Chinese website!