Comma Separated List of SQL Distinct

Question

I am trying to create a list of semicolon names in a table using the following query

DECLARE @listStr VARCHAR(MAX) SELECT @listStr = COALESCE(@listStr+',' ,'') + Name FROM Production.Product SELECT @listStr 

This works fine, however the list contains duplicates

Can anyone tell me how I will do this DISTINCT so that the list does not contain duplicates.

Answer 1

Is this useful?

 DECLARE @listStr VARCHAR(MAX) SELECT @listStr = COALESCE(@listStr+',' ,'') + name FROM (SELECT DISTINCT name FROM Production.Product) t SELECT @listStr