CODEWITHSUNDEEP
sql-servert-sqlfor-xml-pathstuff
Mike Marks
Mike Marks

Reputation: 10129

Is there a way in the STUFF(... FOR XML) function in SQL to prevent duplicate values?

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?

Upvotes: 0

Views: 571

Answers (1)

Joel Coehoorn
Joel Coehoorn

Reputation: 415630

Add a DISTINCT to the inner SELECT query.

Upvotes: 1

Related Questions