I have a table A with field1 that I want to left join into other tables B and C (B & C are queries resultsets). After , matching up this field1 with table B, I want to concat column B2 values for all rows matched on the field1. Similarly for table C - match on field1 from table A and then pick all matched rows in C and jam the column C2 values together with a delimiter say ;.
tblA.field1 tbB.B2;tblB.B2 tblC.C2;tblC.C2
I was thinking of Cross outer apply in MS SQL, but need an equivalent syntax in Oracle 10.
select tblA.field1, listagg(tblB.B2,';') within group (order by tblB.B2), listagg(tblC.C2,';') within group (order by tblC.C2)
left outer join tblB
on tblA.field1 = tblB.field1
left join tblC
on tblA.field1 = tblC.field1
group by tblA.field1