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) pc
Note 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.
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
Here you go
Declare @name varchar(max)
select
@name = COALESCE(@name + ', ','')+name from (select distinct user_email
from
PostedCommentMaster) as t
where
article_id = @id