Multi-row join in one column

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

• What do you think about this tip? E-mail us at editor@searchDatabase.com with your feedback.
• The Best IBM DB2 Web Links: tips, tutorials, scripts, and more.
• Have a DB2 tip to offer your fellow DBA's and developers? The best tips submitted will receive a cool prize--submit your tip today!
• Ask your technical DB2 questions--or help out your peers by answering them--in our live discussion forums.
• Ask the Experts yourself: Our DB2 gurus are waiting to answer your technical questions.