CODEWITHSUNDEEP
sqlsnowflake-cloud-data-platform
Pratik
Pratik

Reputation: 23

Select levels of distinct column value within a row to create hierarchy

below is employee hierarchy table with employee id and it's respective manager id as per his organizational level. employee hierarchy is 203 -202 --201 ---101 ---102

EMPID EMPNAME LV1_ID LV2_ID LV3_ID LV4_ID LV5_ID
101 MAXI 101 101 201 202 203
102 MINI 102 102 201 202 203
201 LENI 201 201 201 202 203
202 TENI 202 202 202 202 203
203 PENI 203 203 203 203 203

expected output is to find employee with heirarchy level of reporting manager id

EMPID EMPNAME RPT1_ID RPT2_ID RPT3_ID
101 MAXI 201 202 203
102 MINI 201 202 203
201 LENI 202 203 NULL
202 TENI 203 NULL NULL
203 PENI NULL NULL NULL

Upvotes: 0

Views: 184

Answers (1)

Simon D
Simon D

Reputation: 6269

This should work provided that you only ever have 5 levels in your initial hierarchy and only want to see the three immediate reports of them:

Setup example

-- Create example table
create or replace table hierarchy
(
    empid   number,
    empname varchar,
    lvl1_id number,
    lvl2_id number,
    lvl3_id number,
    lvl4_id number,
    lvl5_id number
)
;

-- Insert example data
insert overwrite into hierarchy
values (101, 'MAXI', 101, 101, 201, 202, 203),
       (102, 'MINI', 102, 102, 201, 202, 203),
       (201, 'LENI', 201, 201, 201, 202, 203),
       (202, 'TENI', 202, 202, 202, 202, 203),
       (203, 'PENI', 203, 203, 203, 203, 203)
;

Query

-- Query
with unpivoted as (
    select *
    from hierarchy unpivot ( manager_id for level_id in (lvl1_id, lvl2_id, lvl3_id, lvl4_id, lvl5_id) )
),
     unpivoted_managers as (
         select distinct
             up1.empid,
             up2.empname,
             up2.level_id,
             up2.manager_id
         from unpivoted up1
                  left join unpivoted up2 on up1.empid = up2.empid and up1.manager_id != up2.manager_id
         where up1.empid = up1.manager_id
     ),
     rownumbered as (
         select
             upm1.*,
             row_number() over (partition by empid order by level_id) as rown
         from unpivoted_managers upm1
     )

select
    empid,
    empname,
    min("1") as rpt1_id,
    min("2") as rpt2_id,
    min("3") as rpt3_id
from
    rownumbered pivot ( max(manager_id) for rown in (1,2,3))
group by empid, empname
order by empid
;

Results from above query:

+-----+-------+-------+-------+-------+
|EMPID|EMPNAME|RPT1_ID|RPT2_ID|RPT3_ID|
+-----+-------+-------+-------+-------+
|101  |MAXI   |201    |202    |203    |
|102  |MINI   |201    |202    |203    |
|201  |LENI   |202    |203    |NULL   |
|202  |TENI   |203    |NULL   |NULL   |
|203  |NULL   |NULL   |NULL   |NULL   |
+-----+-------+-------+-------+-------+

Upvotes: 0

Related Questions