sql-servert-sqlfor-xml-pathstuff

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?

Solution

  • Add a DISTINCT to the inner SELECT query.