How to Concatenate Emails by ReportId Using SQL Server's GROUP BY and STUFF()?

Aggregating Emails in SQL Server: A GROUP BY and STUFF() Solution

SQL Server's GROUP BY clause is powerful for summarizing data. A frequent task is combining multiple rows' values into a single string, often comma-separated. This is efficiently handled using functions like STUFF().

Let's illustrate with a sample employee report table:

ID	ReportId	Email
1	1	john@example.com
2	2	mary@example.com
3	1	jane@example.com
4	3	david@example.com
5	3	susan@example.com

To concatenate emails associated with each ReportId, using commas as separators, this query works:

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

The STUFF() function modifies a string. It takes four arguments: the string, starting position, number of characters to remove, and the replacement string.

The outer query groups by ReportId. The inner query, using FOR XML PATH(''), concatenates emails for each group. STUFF() then removes the leading comma and space.

The query's output:

ReportId	Email
1	john@example.com, jane@example.com
2	mary@example.com
3	david@example.com, susan@example.com

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