0

To keep this short, here's my SQL code:

SELECT 
    EmailAddress, 
    FormsSubmitted = STUFF(
      (
        SELECT ',' + SourceSubType
        FROM UK_AGT_AgentForms_TEST_DE a 
        WHERE a.EmailAddress = b.EmailAddress
        FOR XML PATH('')
      ), 1, 1, ''),
    DEDate
FROM UK_AGT_AgentForms_TEST_DE b
GROUP BY b.EmailAddress, b.DEDate

And, here's the result set it produces: enter image description here

Is there a way to prevent duplicate values from showing up in the FormsSubmitted column from within the query above? Or do I need to do some "post processing" to remove the duplicates?

Charlieface
  • 52,284
  • 6
  • 19
  • 43
Mike Marks
  • 10,017
  • 17
  • 69
  • 128
  • 1
    Add a `DISTINCT` to the inner SELECT query? – Joel Coehoorn Jun 09 '21 at 18:25
  • @JoelCoehoorn thank you!! That worked. I thought about it but it wasn't very intuitive to me that it would work so I was exploring other options first. Thanks again! Post it as an answer and I'll mark it as the answer. – Mike Marks Jun 09 '21 at 18:27
  • All `STUFF` is doing here is removing the first character of a string; it has no control over the results returned from `FROM`. If you don't want multiple rows of the same value from the dataset, it's the dataset you need to define that in. – Thom A Jun 09 '21 at 18:41
  • You should use this syntax `FOR XML PATH(''), TYPE).value('text()[1]','nvarchar(max)', 1, 1, '')` to avoid issues with XML escaping. And if you have SQL Server 2017+ you should use `STRING_AGG` instead – Charlieface Jun 09 '21 at 19:30

1 Answers1

1

Add a DISTINCT to the inner SELECT query.

Joel Coehoorn
  • 399,467
  • 113
  • 570
  • 794