SQL Distinct comma delimited list

Question

I am trying to create a comma delimted list of names in a table using the below query

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

This works fine, however the list does contain duplicates

Can anyone advise how I would make this DISTINCT so the list does not contain duplicates.

Answer 1

Is it useful ?

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