CODEWITHSUNDEEP
sqlsql-serverssrs-2008
caitlinp
caitlinp

Reputation: 183

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      | [email protected]
1  | Hi      | [email protected]
2  | good    | [email protected]

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      | [email protected]
1  | Hi      | [email protected], [email protected], [email protected]
2  | good    | [email protected], [email protected]

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      | [email protected], [email protected], [email protected]
2  | good    | [email protected], [email protected], [email protected]

The solution I am looking for should return this data:

ID | Summary | Email
---+---------+------------------------------
1  | Hi      | [email protected], [email protected]
2  | good    | [email protected]

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

Upvotes: 2

Views: 2145

Answers (3)

Aaron Dietz
Aaron Dietz

Reputation: 10277

It seems you want this:

SELECT cr.ID,
       cr.Summary,
       STUFF((SELECT DISTINCT ', ' + cr1.Email
              FROM Table1 cr1
              WHERE cr1.ID = cr.ID
              AND cr1.Summary = cr.Summary
              FOR XML PATH('')), 1, 1, '') AS List
FROM Table1 cr
GROUP BY cr.ID, cr.Summary

The inner DISTINCT will prevent the same email from showing up multiple times in a list. The outer GROUP BY will group the email lists in line with their corresponding ID and Summary

Upvotes: 0

John Cappelletti
John Cappelletti

Reputation: 81930

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','[email protected]')
,(1,'Hi','[email protected]')
,(2,'good','[email protected]')


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      [email protected], [email protected]
2   good    [email protected]

Upvotes: 1

Brad
Brad

Reputation: 3591

I think you just need to chagne 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

To 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 cr1.ID, cr1.Summary

Upvotes: 0

Related Questions