Hi,
I have a sql table with below structure:
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:
I tried with a lot of recursive queries with and then pivoting the result, but doesnt work.
Appreciate your help on this.
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
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
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 :(
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.
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
. . .
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
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.
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
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
10 people are following this question.
