Query with hierarchical structure and variable number of hierarchy levels

I have table ‘Areas’ containing customer’s areas:

ID, AREA_NAME, PARENT_ID

Areas are organized in hierarchy up to four levels using PARENT_ID pointing to parent’s AREA_ID. PARENT_ID for top level Area IS NULL.

Table of ‘Customers’ hase AREA_ID referencing the lowest level of area hierarchy:

ID, CUSTOMER_NAME, AREA_ID

I want to get the result table like this:

CUSTOMER_NAME,AREA_LVL_1,AREA_LVL_2,AREA_LVL_3,AREA_LVL_4

The problem here is that customer’s area (AREA_ID) does not allways point to the lowest fourth level of areas. Sometimes it points to fourth level AREA_ID, sometimes third and so on.

Depending of area levels, the resulting table should look like:

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>

I don’t know how to make this recursion with variable number of levels inside single SQL query. I need just single SQL query (not procedure with cursor passing the recursion loop).

Answer

You need a recursive CTE that returns all the levels for each area which will be joined to customers:

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

See the demo.
Results:

> 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