Create a Comma Delimited List Using SELECT Clause From Table Column

I should keep reference to this:


DECLARE @listIntoString VARCHAR(MAX)

    SELECT   @listIntoString = COALESCE(@listIntoString+',' ,'') +  COALESCE( color,'')
    FROM production.product
    SELECT @listIntoString, len(@listIntoString)

if column is null, we are just adding empty string.

If you want distinct colors in delimited list:

 DECLARE @listIntoString VARCHAR(MAX)
with cte as
(    select distinct color from production.product where color is not null
)
SELECT    @listIntoString = COALESCE(@listIntoString+',' ,'') +  COALESCE( color,'')
FROM cte
SELECT @listIntoString, len(@listIntoString)