Multi-row join in one column

How to join two or more rows from tables and put them in one column in DB2.

All of you are aware how to join columns, but this example will show you how to join two or more rows from tables

and put them in one column.

create table multi_row_a 
(cola char(7) 
,colb smallint 
,a_pk integer not null primary key 
,name varchar(15) 
) 

create table multi_row_b 
(b_pk     integer not null 
,b_unique integer not null 
,value    varchar(15) 
,comments varchar(50) 
,primary key (b_pk, b_unique) 
) 

insert into multi_row_a(a_pk, name) values 
 (1,'dogs') 
,(2,'Cats') 
,(3,'Lizard') 
,(4,'robin') 

insert into multi_row_b(b_pk, b_unique, value) values 
 (1, 1, 'mammal') 
,(1, 2, '4 legs') 
,(1, 3, 'furry') 
,(2, 1, 'mammal') 
,(2, 2, '4 legs') 
,(2, 3, 'furry') 
,(3, 2, '4 legs') 
,(3, 3, 'furry') 

Now sample data were prepared. Here is the result of a simple OUTER JOIN.

 
select multi_row_a.name, multi_row_b.value 
  from multi_row_a 
       left outer join 
       multi_row_b 
       on multi_row_a.a_pk = multi_row_b.b_pk 


NAME            VALUE           
--------------- --------------- 
Dog             mammal          
Dog             4 legs          
Dog             furry           
Cat             mammal          
Cat             4 legs          
Cat             furry           
Lizard          4 legs          
Lizard          furry           
Robin           -               

To get all the rows into one column:

with 
 b_with_subseq (b_pk, subseq, value) as 
(select b_pk 
       ,rownumber() over(partition by b_pk order by b_unique) 
       ,value 
   from multi_row_b 
) 

,concat_b (subseq, b_pk, value_list) as 
(select subseq, b_pk, varchar(value, 100) 
   from b_with_subseq 
  where subseq = 1 
 union all 
 select bs.subseq, bs.b_pk, value_list || ', ' || value 
   from concat_b      ct 
       ,b_with_subseq bs 
  where bs.subseq = ct.subseq + 1 
    and bs.b_pk = ct.b_pk 
    and ct.subseq < 100 
) 

select a.name, b.value_list 
  from multi_row_a a 
       left outer join 
       concat_b b 
       on a.a_pk = b.b_pk 
 where b.subseq = (select max(subseq) 
                     from concat_b bs 
                    where bs.b_pk = b.b_pk 
                  ) 
   or b.subseq is null 
order by a.a_pk 


NAME            VALUE_LIST                                                   
--------------- ------------------ 
Dog             mammal, 4 legs, furry                                                                                
Cat             mammal, 4 legs, furry                                                                                
Lizard          4 legs, furry                                                                                        
Robin           -

For More Information


This was first published in January 2002

Dig deeper on DB2 UDB (universal databases)

0 comments

Oldest 

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to:

-ADS BY GOOGLE

SearchEnterpriseLinux

SearchDataCenter

Close