Tip

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


This was first published in January 2002

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

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:

Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.