How can I get distinct values in COALESCE()
You can wrap the select statement into a subselect and apply coalesce on the results.
Declare @name varchar(max) select @name = COALESCE(@name + ', ','') + user_email from (select distinct user_email from PostedCommentMaster where article_id = @id) pcNote that this uses an undocumented feature of SQL Server to concatenate the results into one string. While I can't find a link to it anymore, I recall reading that your should not rely on this behavior.
A better alternative would be to use the FOR XML syntax to return a concatenated string. A search on SO returns multiple results you can use as an example.
Here you go
Declare @name varchar(max) select @name = COALESCE(@name + ', ','')+name from (select distinct user_email from PostedCommentMaster) as twhere article_id = @id
You can use group by for unique values.
Declare @name varchar(max)select @name = COALESCE(@name + ', ','')+ user_email from PostedCommentMaster where article_id = @id group by user_email