How to Concatenate Unique Values into a Comma-Separated String Using SQL Server's GROUP BY?

Aggregating Unique Values into a Comma-Separated List with SQL Server's GROUP BY

Challenge:
Given a table with duplicate entries, the goal is to group data by a specific column and consolidate unique values from another column into a single, comma-separated string.

Sample Data:

The following table illustrates the initial dataset:

ID	ReportId	Email
1	1	[email protected]
2	2	[email protected]
3	1	[email protected]
4	3	[email protected]
5	3	[email protected]

Target Result:

The desired outcome is a table with consolidated email addresses:

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

Solution using GROUP BY and STUFF:

The STUFF function is employed to efficiently concatenate strings, removing leading characters. The solution is as follows:

<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 core logic involves a subquery within the STUFF function. This subquery iterates through all rows sharing the same ReportId as the outer query's current row. For each matching row, it appends a comma and the Email value to the accumulating string. The outer query then uses GROUP BY to aggregate these concatenated email strings for each unique ReportId. The STUFF function removes the initial comma and space.

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