Search code examples
sqlsql-serverssrs-2008

Comma separated values in SQL Server returning duplicates

I am writing a statement to create a comma-separated list of column values based on its unique ID value. Some IDs have multiple email values.

I have a ID, Email, Summary columns.

For instance:

ID | Summary | Email
---+---------+----------------
1  | Hi      | abc@gmail.com
1  | Hi      | def@gmail.com
2  | good    | xyz@gmail.com

Looking through a bunch of articles and stack overflow questions, I tried this: 

SELECT
     STUFF((SELECT ', ' + cr.Email AS [text()]
            FROM Table1 cr1
            WHERE cr1.Email = cr.Email
            FOR XML PATH('')), 1, 1, '') AS List
FROM  
    Table1 cr
GROUP BY 
    cr.Email

The problem with the above query is that, it shows me the emails, but they are all repetitive. For example:

ID | Summary | Email
---+---------+---------------------------------------------
1  | Hi      | abc@gmail.com
1  | Hi      | def@gmail.com, def@gmail.com, def@gmail.com
2  | good    | xyz@gmail.com, xyz@gmail.com

So I tried a different approach, 

DECLARE @tmp VARCHAR(250)
SET @tmp = ''

SELECT @tmp = @tmp + cr.Email + ', ' 
FROM Table1 cr

SELECT
    SUBSTRING(@tmp, 0, LEN(@tmp))

The problem with the above query is that, it shows me every email in a comma separated list. So every row with a unique ID has all the emails in the 

ID | Summary | Email
---+---------+--------------------------------------------
1  | Hi      | abc@gmail.com, def@gmail.com, xyz@gmail.com
2  | good    | abc@gmail.com, def@gmail.com, xyz@gmail.com

The solution I am looking for should return this data:

ID | Summary | Email
---+---------+------------------------------
1  | Hi      | abc@gmail.com, def@gmail.com
2  | good    | xyz@gmail.com

What can I do to improve my query, or am I completely steering towards the wrong direction?

Solution

  • Seems you should key off of ID/Summary and not Email

    Example

    Declare @YourTable Table ([ID] int,[Summary] varchar(50),[Email] varchar(50))
Insert Into @YourTable Values 
 (1,'Hi','abc@gmail.com')
,(1,'Hi','def@gmail.com')
,(2,'good','xyz@gmail.com')


Select A.ID
      ,A.Summary
      ,EMail   = Stuff((Select Distinct ', ' +EMail From @YourTable Where ID=A.ID and Summary=A.Summary For XML Path ('')),1,2,'') 
 From @YourTable A
 Group By ID,Summary

    Returns

    ID  Summary EMail
1   Hi      abc@gmail.com, def@gmail.com
2   good    xyz@gmail.com