question

DDDPPPP-7114 avatar image
0 Votes"
DDDPPPP-7114 asked DDDPPPP-7114 commented

Need help with recursive query for finding hierarchy

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.

sql-server-transact-sql
capture.png (7.1 KiB)
capture1.png (3.1 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Viorel-1 avatar image
0 Votes"
Viorel-1 answered DDDPPPP-7114 commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Thanks, it worked for me.. :)

0 Votes 0 ·
Viorel-1 avatar image
0 Votes"
Viorel-1 answered Viorel-1 edited

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


· 3
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi, Thanks for this, but it gives a lot of records. Probably some join issue.
I just have 1700 records in the table. but i get 50000 records with this query :(

0 Votes 0 ·

The general recommendation for this type of question is that you post CREATE TABLE statements for your tables in combination with INSERT statements with sample data, enough to illustrate all angles of the problem. Add to that the desired output of the sample.

This permits us to copy and paste into a query window to develop a tested solution.

When you don't do this, you will get guesses from shots and the hip, and, as you have noticed, they may not be spot on.

0 Votes 0 ·

For the shown pictures, it gives 15 results, which seems correct, but maybe that pictures were not enough to explain the problem. Give the requested details and try a variation:

 . . .
     left join MyTable t2 on t2.Reporting_Manager_id = t1.employeeId and t2.managerId = t1.managerId
     left join MyTable t3 on t3.Reporting_Manager_id = t2.employeeId and t3.managerId = t2.managerId
     left join MyTable t4 on t4.Reporting_Manager_id = t3.employeeId and t4.managerId = t3.managerId
  . . .
0 Votes 0 ·
pituach avatar image
0 Votes"
pituach answered

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


5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft avatar image
0 Votes"
MelissaMa-msft answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

DDDPPPP-7114 avatar image
0 Votes"
DDDPPPP-7114 answered MelissaMa-msft commented

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

· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @DDDPPPP-7114,

Thanks for your update.

In your situation, there is no need to use recursive queries or pivot. You could use multiple joins to achieve this requirement.

Please refer below Viorel's answer.

Thank you for understanding!

Best regards
Melissa

0 Votes 0 ·