How can I get distinct values in COALESCE()

Question

I have table values in this format

sam
jack
sam
john

Declare @name varchar(max)
select @name = COALESCE(@name + ', ','')+ user_email 
from   PostedCommentMaster where article_id = @id

How can I get distinct value

sam,jack,john

like this.

Answer 1

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.

Answer 2

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

Answer 3

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