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.
This was first published in January 2002