SQL Server 2019

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.