How to list out management levels from Postgres table

I have a table with the following fields: Id, manager_id, and candidate_name

The manager_id will point at id which allows me to reference the management chain. I want to generate an output like the following:

id | candidate_name | manager_id | lvl1 mgrID | lvl1 mgr candidate name | lvl2 mgrID | lvl2 mgr candidate name | etc…

I ultimately want to build out a csv of this structure so that I can leverage it in reports.

I’ve used the following query so far to get close to this:

CREATE EXTENSION tablefunc;

SELECT * FROM connectby('job', 'id', 'manager_id', '261', 0, ',') AS t(id int, manager_id int, level int, ord text) order by id;

Which outputs the following:

  id   | manager_id | level |                       ord                        
-------+------------+-------+--------------------------------------------------
     2 |         12 |     3 | 261,226,12,2
     3 |          2 |     4 | 261,226,12,2,3
     4 |        106 |     4 | 261,226,110,106,4
     5 |          4 |     5 | 261,226,110,106,4,5
     6 |         86 |     4 | 261,226,12,86,6
     7 |        920 |     6 | 261,226,12,86,6,920,7
     8 |         86 |     4 | 261,226,12,86,8
     9 |          8 |     5 | 261,226,12,86,8,9
    10 |        145 |     5 | 261,226,12,4209,145,10
    11 |        139 |     7 | 261,226,12,4209,145,10,139,11
    12 |        226 |     2 | 261,226,12
    13 |       4209 |     4 | 261,226,12,4209,13
    14 |        159 |     5 | 261,226,12,69,159,14
    15 |         14 |     6 | 261,226,12,69,159,14,15
    16 |        110 |     3 | 261,226,110,16

The ord column gives me the management chain, but I’m hitting a wall on how to generate the columns with the mgr levels that I’m looking for. This doesn’t have to exist just in SQL either.

Note that the management levels can go into the teens as far as depth goes. Appreciate any thoughts on how to approach this.

Answer

If you know in advance the maximum number of levels in the hierarchy (say, 3), one option uses a recursive query and conditional aggregation:

with recursive cte as (
    select id employee_id, id, manager_id, candidate_name, 0 lvl from mytable
    union all
    select c.employee_id, t.id, t.manager_id, t.candidate_name, lvl + 1
    from cte c
    inner join mytable t on t.id = c.manager_id
)
select 
    employee_id,
    max(case when lvl = 1 then id             end) level1_manager_id,
    max(case when lvl = 1 then candidate_name end) level1_candidate_name,
    max(case when lvl = 2 then id             end) level2_manager_id,
    max(case when lvl = 2 then candidate_name end) level2_candidate_name
    max(case when lvl = 2 then id             end) level3_manager_id,
    max(case when lvl = 2 then candidate_name end) level3_candidate_name
from cte
group by employee_id

You can expand the where clause with more columns to handle additional levels. When the hierarchy of a given employee exhausts, further columns show null values.

On the other hand, if you want to dynamically create columns according to the employee depth in the hierarchy, then you need dynamic SQL, which is much more complicated.