By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
with
resources (parent_resource, child_resource, resource_, resource_type) as (
select 'DE Hospital', 'DE Section', 'DE Lab', 'Lab' from dual union all
select 'DE Lab', 'DF SubSection', 'DF Section', 'Section' from dual union all
select 'DE Section', 'DE bench', 'DF SubSection', 'Bench' from dual union all
select 'DE Section', 'DF bench', 'DF SubSection', 'Bench' from dual union all
select 'DE Section', 'DG bench', 'DF SubSection', 'Bench' from dual ),
orders(order_id, resource_type, resource_) as (
select 12345, 'SubSection', 'DF SubSection' from dual union all
select 23456, 'Bench', 'DG bench' from dual union all
select 34567, 'Section', 'DE Section' from dual ),
hierarchy as (
select r.*, level, connect_by_root(child_resource) root
from resources r
connect by prior resource_ = child_resource
or resource_ = prior parent_resource)
select order_id, root,
max(case h.resource_type when 'Lab' then h.parent_resource end) hospital,
max(case h.resource_type when 'Lab' then h.resource_ end) lab,
max(case h.resource_type when 'Lab' then h.child_resource end) section,
max(case h.resource_type when 'Section' then h.child_resource end) subsection,
max(case h.resource_type when 'Bench' then h.child_resource end) bench
from orders o join hierarchy h on h.root = o.resource_
group by order_id, root order by order_id
| ORDER_ID | ROOT | HOSPITAL | LAB | SECTION | SUBSECTION | BENCH |
|---|---|---|---|---|---|---|
| 12345 | DF SubSection | DE Hospital | DE Lab | DE Section | DF SubSection | null |
| 23456 | DG bench | DE Hospital | DE Lab | DE Section | DF SubSection | DG bench |
| 34567 | DE Section | DE Hospital | DE Lab | DE Section | null | null |