Concatenate the row values after join

Question

How to concatenate the row values after joining Table1 and table2.

Table 1:

-----
Col1     
------
  1      
  2

Table 2:

-----------------
Col1         Col2
-----------------
 1             A
 1             B
 1             C
 2             D
 2             E
 2             F

Desired Result:

-----------------
Col1         Col2
-----------------
 1          A,B,C
 2          D,E,F

http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Deepshikha, Nov 03 '14 at 06:02
Check this http://stackoverflow.com/questions/194852/concatenate-many-rows-into-a-single-text-string — Arunprasanth K V, Nov 03 '14 at 06:06

score 3 · Accepted Answer · answered Nov 03 '14 at 05:59

Try this:

create table #table1(
    col1 int
)
create table #table2(
    col1 int,
    col2 char(1),
)
insert into #table1
select 1 union all 
select 2

insert into #table2
select 1, 'A' union all
select 1, 'B' union all
select 1, 'C' union all
select 2, 'D' union all
select 2, 'E' union all
select 2, 'F'


select
    col1,
    col2 = 
        stuff((
            select
                ', ' + t2.col2
            from #table2 t2
            where
                t2.col1 = t1.col1
            group by t2.col2
            for xml path(''), type).value('.', 'varchar(max)'
        ), 1, 2, '')
from #table1 t1

drop table #table1
drop table #table2

score 0 · Answer 2 · answered Nov 03 '14 at 06:05

0

Mysql:

SELECT group_concat(table2.col2) FROM 
   table2 JOIN table1 ON table1.col1 = table2.col1 
GROUP BY table2.col1

answered Nov 03 '14 at 06:05

gvgvgvijayan

1,851
18
34

Dudi Konfino · Answer 3 · 2014-11-03T10:27:46.073

You can use cursor as the following cod.

checked for syntax only

create table #Desired_Result(col1 int,col2 varchar(20))
DECLARE cur cursor FAST_FORWARD READ_ONLY
FOR
 SELECT  col1,col2

DECLARE @d int
declare @l varchar(20)

declare @str1 varchar(30)=''
declare @str2 varchar(30)=''
OPEN cur
 FETCH NEXT FROM cur INTO @d,@l
 WHILE @@FETCH_STATUS=0
 BEGIN 
if @d=1
set @str1=@str1+@l+','
else
if @d=2
set @str2=@str2+@l+','
FETCH NEXT FROM cur  INTO @d,@l
END
@str1=substring(@str1,1,len(@str1)-1)
@str2=substring(@str2,1,len(@str2)-1)
insert into #Desired_Result values (col1,col2)(1,@str1)
insert into #Desired_Result values (col1,col2)(2,@str2)

select * from #Desired_Result

Close  cur 
DEALLOCATE cur

Concatenate the row values after join

3 Answers3