By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table areas(ID int, AREA_NAME varchar(10), PARENT_ID int);
insert into areas(ID, AREA_NAME, PARENT_ID) values
(1, 'A', null), (2, 'A1', 1), (3, 'A13', 2), (4, 'A136', 3), (5, 'A2', 1), (6, 'A24', 5),
(7, 'B', null), (8, 'B2', 7);
8 rows affected
create table customers(ID int, CUSTOMER_NAME varchar(20), AREA_ID int);
insert into customers(ID, CUSTOMER_NAME, AREA_ID) values
(1, 'John', 4), (2, 'Maria', 8), (3, 'Steve', 6);
3 rows affected
with
cte as (
select id, area_name, parent_id, id start, 1 level from areas
union all
select a.id, a.area_name, a.parent_id, c.start, c.level + 1
from areas a inner join cte c
on c.parent_id = a.id
where c.parent_id is not null
),
levels as (
select id, area_name, parent_id, start,
max(level) over (partition by start) - level + 1 level
from cte
)
select c.customer_name,
max(case when l.level = 1 then l.area_name end) area_lvl_1,
max(case when l.level = 2 then l.area_name end) area_lvl_2,
max(case when l.level = 3 then l.area_name end) area_lvl_3,
max(case when l.level = 4 then l.area_name end) area_lvl_4
from customers c left join levels l
on l.start = c.area_id
group by c.id, c.customer_name
| customer_name | area_lvl_1 | area_lvl_2 | area_lvl_3 | area_lvl_4 |
|---|---|---|---|---|
| John | A | A1 | A13 | A136 |
| Maria | B | B2 | null | null |
| Steve | A | A2 | A24 | null |
Warning: Null value is eliminated by an aggregate or other SET operation.