title: Comma separated values in SQL Server returning duplicates
body: <p>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.</p>
  
  <p>I have a <code>ID, Email, Summary</code> columns. </p>
  
  <p>For instance:</p>
  
  <pre><code>ID | Summary | Email
  ---+---------+----------------
  1  | Hi      | abc@gmail.com
  1  | Hi      | def@gmail.com
  2  | good    | xyz@gmail.com
  </code></pre>
  
  <p>Looking through a bunch of articles and stack overflow questions, I tried this: </p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>The problem</strong> with the above query is that, it shows me the emails, but they are all repetitive. For example:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>So I tried a different approach, </p>
  
  <pre><code>DECLARE @tmp VARCHAR(250)
  SET @tmp = ''
  
  SELECT @tmp = @tmp + cr.Email + ', ' 
  FROM Table1 cr
  
  SELECT
      SUBSTRING(@tmp, 0, LEN(@tmp))
  </code></pre>
  
  <p><strong>The problem</strong> 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 </p>
  
  <pre><code>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
  </code></pre>
  
  <p><strong>The solution</strong> I am looking for should return this data:</p>
  
  <pre><code>ID | Summary | Email
  ---+---------+------------------------------
  1  | Hi      | abc@gmail.com, def@gmail.com
  2  | good    | xyz@gmail.com
  </code></pre>
  
  <p>What can I do to improve my query, or am I completely steering towards the wrong direction? </p>