Need help with recursive query for finding hierarchy

DhanashreePrasun 61 Reputation points
2021-03-19T09:31:08.837+00:00

Hi,
I have a sql table with below structure:79539-capture.png

I want to write a query which will display the levels with all the reporting manager ids.. There can only be 5 levels.
So i want something like below:

79499-capture1.png

I tried with a lot of recursive queries with and then pivoting the result, but doesnt work.
Appreciate your help on this.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,555 questions
0 comments
Accepted answer
  1. Viorel 112.5K Reputation points
    2021-03-22T14:38:41.84+00:00

    I tried with the queries given, but the output isnt as expected

    Did you try all of suggestions? For example: 

    declare @emp table  
(  
    managerid int,  
    employeeid varchar(20),  
    Reporting_Manager_id varchar(20)  
)  
  
insert into @emp values  
(1371,'F4',null),  
(1371,'F4.10','F4'),  
(1371,'F4.20','F4'),  
(1371,'F4.20.10','F4.20'),  
(1371,'F4.20.20','F4.20'),  
(1371,'F4.20.20.00','F4.20.20'),  
(1371,'F8',null),  
(1371,'F11',null),  
(1371,'F11.10','F11')  
  
-- expected results:  
  
select '1371' Level1,'F4' Level2, null Level3, null Level4, null Level5  
union  
select '1371', 'F4', null, null, null  
union  
select '1371', 'F4', 'F4.10',null, null  
union  
select '1371', 'F4','F4.20', null, null  
union  
select '1371','F4','F4.20','F4.20.10',null  
union  
select '1371','F4','F4.20','F4.20.20',null  
union  
select '1371','F4','F4.20','F4.20.20','F4.20.20.00'  
union  
select '1371', 'F8', null, null, null  
union  
select '1371', 'F11', null, null, null  
union  
select '1371', 'F11', 'F11.10', null, null  
  
---  
  
select distinct *  
from   
(  
    select  
        t1.managerid as Level1,   
        t1.employeeId as Level2,   
        t2.employeeId as Level3,   
        t3.employeeId as Level4,  
        t4.employeeId as Level5  
   from      @emp t1  
   left join @emp t2 on t2.Reporting_Manager_id = t1.employeeId and t2.managerId = t1.managerId  
   left join @emp t3 on t3.Reporting_Manager_id = t2.employeeId and t3.managerId = t2.managerId  
   left join @emp t4 on t4.Reporting_Manager_id = t3.employeeId and t4.managerId = t3.managerId       
   where t1.Reporting_Manager_id is null  
) t  
group by grouping sets  
(  
    (Level1, Level2, Level3, Level4, Level5),   
    (Level1, Level2, Level3, Level4),   
    (Level1, Level2, Level3),   
    (Level1, Level2)  
)  
order by Level1, Level2, Level3, Level4, Level5

4 additional answers

Sort by: Most helpful
Most helpful Newest Oldest
  1. Viorel 112.5K Reputation points
    2021-03-19T13:01:14.49+00:00

    Try something like this: 

    select distinct *
from 
(
    select
        t1.managerid as Level1, 
        t1.employeeId as Level2, 
        t2.employeeId as Level3, 
        t3.employeeId as Level4,
        t4.employeeId as Level5
    from MyTable t1
    left join MyTable t2 on t2.Reporting_Manager_id = t1.employeeId
    left join MyTable t3 on t3.Reporting_Manager_id = t2.employeeId
    left join MyTable t4 on t4.Reporting_Manager_id = t3.employeeId
    where t1.Reporting_Manager_id is null
) t
group by grouping sets
(
    (Level1, Level2, Level3, Level4, Level5), 
    (Level1, Level2, Level3, Level4), 
    (Level1, Level2, Level3), 
    (Level1, Level2)
)
order by Level1, Level2, Level3, Level4, Level5
  2. Ronen Ariely 15,096 Reputation points
    2021-03-20T21:20:57.073+00:00

    Please provide

    (1) Queries to create the table and insert the sample data
    (2) The exact result set which you expect to get according to the sample data
    (3) Please explain how you get the result from the source
    (4) Please provide the version of the server which you use

    Note! In first glance looking at the image of the source data, I am guessing that we will not need to use any recursive or other type of loop, but I am not sure if what you provided in the second image is the full result set or part of it and what is the logic you used to get only these 4 rows out of the source data

    One more point to clarify: you speak about 5 levels but your images shows 4 levels + managerid which you present as level 1. It is HUGE different if you need 4 levels or 5 since if I get your scenario right then you only have 4 levels which mean that you can split the data in the column employeeId directly using the function PARSENAME

    0 comments
  3. MelissaMa-MSFT 24,176 Reputation points
    2021-03-22T08:21:37.45+00:00

    Hi @DDDPPPP-7114,

    Welcome to Microsoft Q&A!

    Please also refer below and check whether it is helpful: 

    drop table if exists tabledd  
  
create table tabledd  
(managerid int,  
emmployeeid varchar(20),  
Reporting_Manager_id varchar(20))  
  
insert into tabledd values  
(1371,'F4',null),  
(1371,'F4.10','F4'),  
(1371,'F4.20','F4'),  
(1371,'F4.20.00','F4.20'),  
(1371,'F4.20.10','F4.20'),  
(1371,'F4.20.20','F4.20'),  
(1371,'F4.20.30','F4.20'),  
(1371,'F4.20.40','F4.20'),  
(1371,'F4.30','F4'),  
(1371,'F4.30.00','F4.30'),  
(1371,'F4.30.10','F4.30'),  
(1371,'F4.30.20','F4.30'),  
(1371,'F4.30.30','F4.30'),  
(1371,'F4.30.40','F4.30'),  
(1371,'F4.40','F4')  
  
;WITH  MyCTE  
AS  
(  
    SELECT  managerid, Reporting_Manager_id,emmployeeid,  1 AS [Level]  
    FROM    tabledd t1  
    WHERE   Reporting_Manager_id IS NULL  
    UNION ALL  
    SELECT  t2.managerid, T2.Reporting_Manager_id,T2.emmployeeid emmployeeid1,M.[level] + 1 AS [Level]  
    FROM    tabledd AS t2  
            JOIN MyCTE AS M ON t2.Reporting_Manager_id = M.emmployeeid     
)  
select a.managerid level1,a.emmployeeid level2  
,IIF(level=2,b.emmployeeid,b.Reporting_Manager_id) level3  
,IIF(level=3,b.emmployeeid,NULL) level4  
,IIF(level=4,b.emmployeeid,NULL) level5  
from tabledd a  
join MyCTE b on a.managerid=b.managerid  
where a.Reporting_Manager_id IS NULL  
order by 1,2,3,4,5

    Output: 

    level1	level2	level3	level4	level5  
1371	F4	NULL	NULL	NULL  
1371	F4	F4.10	NULL	NULL  
1371	F4	F4.20	NULL	NULL  
1371	F4	F4.20	F4.20.00	NULL  
1371	F4	F4.20	F4.20.10	NULL  
1371	F4	F4.20	F4.20.20	NULL  
1371	F4	F4.20	F4.20.30	NULL  
1371	F4	F4.20	F4.20.40	NULL  
1371	F4	F4.30	NULL	NULL  
1371	F4	F4.30	F4.30.00	NULL  
1371	F4	F4.30	F4.30.10	NULL  
1371	F4	F4.30	F4.30.20	NULL  
1371	F4	F4.30	F4.30.30	NULL  
1371	F4	F4.30	F4.30.40	NULL  
1371	F4	F4.40	NULL	NULL

    Best regards
    Melissa

    If the answer is helpful, please click "Accept Answer" and upvote it.
    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

    0 comments
  4. DhanashreePrasun 61 Reputation points
    2021-03-22T12:52:39.213+00:00

    drop table if exists emp

    create table emp
    (managerid int,
    employeeid varchar(20),
    Reporting_Manager_id varchar(20))

    insert into emp values
    (1371,'F4',null),
    (1371,'F4.10','F4'),
    (1371,'F4.20','F4'),
    (1371,'F4.20.10','F4.20'),
    (1371,'F4.20.20','F4.20'),
    (1371,'F4.20.20.00','F4.20.20'),
    (1371,'F8',null),
    (1371,'F11',null),
    (1371,'F11.10','F11')

    And below is what is the result that is expected as output with a query:
    select '1371' Level1,'F4' Level2, null Level3, null Level4, null Level5
    union
    select '1371', 'F4', null, null, null
    union
    select '1371', 'F4', 'F4.10',null, null
    union
    select '1371', 'F4','F4.20', null, null
    union
    select '1371','F4','F4.20','F4.20.10',null
    union
    select '1371','F4','F4.20','F4.20.20',null
    union
    select '1371','F4','F4.20','F4.20.20','F4.20.20.00'
    union
    select '1371', 'F8', null, null, null
    union
    select '1371', 'F11', null, null, null
    union
    select '1371', 'F11', 'F11.10', null, null

    I tried with the queries given, but the output isnt as expected