user129107 user129107 - 5 months ago 6
SQL Question

Merge multiple join records into one by id

Better title? Please edit.

Having two tables:

# table1
id name desc
--------------------
1 Nim blah
2 Rod blah
3 Nero blah

# table2
id_table1 rank url
----------------------
1 1 U11
1 2 U12
1 3 U13
2 1 U21
3 1 U31
3 2 U32


Currently I am using a JOIN query and typically get:

id name desc url
---------------------
1 Nim blah U11
1 Nim blah U12
1 Nim blah U13
2 Rod blah U21
3 Nero blah U31
3 Nero blah U32


I am wondering if there is a query that will deliver something like:

id name desc url(1) url(2) url(3)
-------------------------------------
1 Nim blah U11 U12 U13
2 Rod blah U21 NULL NULL
3 Nero blah U31 U32 NULL

Answer

Assuming you know the number of potential columns, one option is to use conditional aggregation to pivot your results:

select t1.id, t1.name, t1.desc, 
       max(case when t2.rank = 1 then t2.url end) url1,
       max(case when t2.rank = 2 then t2.url end) url2,
       max(case when t2.rank = 3 then t2.url end) url3
from table1 t1
       join table2 t2 on t1.id = t2.id_table1
group by t1.id, t1.name, t1.desc