ROW VALUE CONCATE STUFF USING XML PATH , SUBSTRING USING XML PATH , COALESCE

 

-- #1 using stuff function and xml path
SELECT STUFF(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),1,1,'')

-- #2 using substring function and xml path
SELECT SUBSTRING(
    (SELECT ',' + product_category_name
    FROM dbo.product_category
    FOR XML PATH ('')),2,10000)

-- #4 using coalesce when there is null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + COALESCE(product_category_name,'')
FROM dbo.product_category
SELECT @concat_string;
GO

-- #4 using coalesce when there is not any null value
DECLARE @concat_string VARCHAR(MAX);
SELECT @concat_string = COALESCE(@concat_string+',','') + product_category_name
FROM dbo.product_category 
WHERE product_category_name IS NOT NULL
SELECT @concat_string;
GO