Here's another way... it's kind of a "Quirky-Coalesce":

-- sample data

DECLARE @Production_Product TABLE (name varchar(10));

INSERT INTO @Production_Product VALUES ('aaa'),('bbb'),('ccc'),('ccc');

DECLARE @listStr varchar(max)='';

SELECT @listStr=@listStr+

CASE

WHEN @listStr='' THEN ''+name

ELSE ','+name

END

FROM (SELECT DISTINCT name FROM @Production_Product) AS xxx

SELECT @listStr;

GO

This will produce the same query plan as the query that Chris put together but I also included a CASE statement that prevents a leading comma.