asked on
Cross Outer Join
Hi,
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.
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.
Oracle Database
Last Comment
LuckyLucks
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
"Cross outer apply" does not exist
I guess you mean either an "outer apply" or "cross apply"
In oracle 12c i believe you can use:
"cross join lateral"
But as yet i still have not had a need to use it.
I guess you mean either an "outer apply" or "cross apply"
In oracle 12c i believe you can use:
"cross join lateral"
But as yet i still have not had a need to use it.
ASKER
thanks
select tblA.field1, listagg(tblB.B2,';') within group (order by tblB.B2), listagg(tblC.C2,';') within group (order by tblC.C2)
from tblA
left outer join tblB
on tblA.field1 = tblB.field1
left join tblC
on tblA.field1 = tblC.field1
group by tblA.field1