Geek asks and answers

How can I get different 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 a great value

sam,jack,john

like this.

+7
source share
6 answers

You can wrap the select statement in a subquery and apply a combination of 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 the undocumented SQL Server function to combine the results on a single line. Although I can no longer find a link to it, I recall that you should not rely on this behavior.

A better alternative would be to use syntax FOR XMLto return a concatenated string. A SO search returns a few results that you can use as an example.

+19

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

group by .

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

Best .

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

SELECT distinct @name = COALESCE (@name + ',', '') + user_email from "AddedCommentMaster", where article_id = @id

0
source

I am stuck on the same issue. So my solution to this is-

  1. Create the #temp table.
  2. Insert various values ​​into your #temp table.

  3. Add your combined request.

    create table #emailList (user_email varchar(200))
insert into #emailList select distinct user_email from   PostedCommentMaster 
where article_id = @id

Declare @name varchar(max)
select @name = COALESCE(@name + ', ','')+ user_email 
from   #emailList

    It is also useful when retrieving data from multiple tables using joins.

0
source

More articles:


All Articles