Consider the below Parent_Child Table, where A is the parent of B and C and B is the parent of D and E and so on..
Expected output:
Query :
with A(Element,lev) as ( select c1,0 from parent_child union select c2,1 from parent_child union select c3,2 from parent_child union select c4,3 from parent_child ), Rel(parent,child) as ( select distinct c1,c2 from parent_child union select distinct c2,c3 from parent_child union select distinct c3,c4 from parent_child ) select row_number() over(order by lev) as id, A.Element, A.lev, (select parent from rel where child=A.Element) as parent_id from A
-----------------------Table creation Queries---------------------------------------------------------------------
create table parent_child ( c1 varchar2(5), c2 varchar2(5), c3 varchar2(5), c4 varchar2(5) ); insert into parent_child values('A','B','D','H'); insert into parent_child values('A','B','D','I'); insert into parent_child values('A','B','E',NULL); insert into parent_child values('A','C','F',NULL); insert into parent_child values('A','C','G',NULL); commit;
No comments:
Post a Comment