Sudik Maharana Sudik Maharana - 2 months ago 16
SQL Question

How can I get distinct values in COALESCE()

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

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.