Evaluate Weigh the pros and cons of technologies, products and projects you are considering.

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 

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 
       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:

 b_with_subseq (b_pk, subseq, value) as 
(select b_pk 
       ,rownumber() over(partition by b_pk order by b_unique) 
   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

Dig Deeper on DB2 UDB (universal databases)

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.