Last week, I submitted an entry for T-SQL challenge 8. This time, the contestants were asked to process a hierarchy using recursive CTEs without applying any filters inside the CTE. The big challenge was the condition that the CTE should not contain any filter for a specific manager.
Here is some code for populating test data.
--Populate test data DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT) INSERT INTO @Employees(EmpID, EmpName, ReportsTo) SELECT 1, 'Jacob', NULL UNION ALL SELECT 2, 'Rui', NULL UNION ALL SELECT 3, 'Jacobson', NULL UNION ALL SELECT 4, 'Jess', 1 UNION ALL SELECT 5, 'Steve', 1 UNION ALL SELECT 6, 'Bob', 1 UNION ALL SELECT 7, 'Smith', 2 UNION ALL SELECT 8, 'Bobbey', 2 UNION ALL SELECT 9, 'Steffi', 3 UNION ALL SELECT 10, 'Bracha', 3 UNION ALL SELECT 11, 'John', 5 UNION ALL SELECT 12, 'Michael', 6 UNION ALL SELECT 13, 'Paul', 6 UNION ALL SELECT 14, 'Lana', 7 UNION ALL SELECT 15, 'Johnson', 7 UNION ALL SELECT 16, 'Mic', 8 UNION ALL SELECT 17, 'Stev', 8 UNION ALL SELECT 18, 'Paulson', 9 UNION ALL SELECT 19, 'Jessica', 10
I started by creating a recursive CTE for the whole table like this:
;With Hierarchy(EmpName, EmpID, Level, FullyQualifiedName)
As
(
Select E.EmpName, E.EmpID, 0, Cast('.'+E.EmpName+'.' as Varchar(MAX))
From @Employees E
Where E.ReportsTo is null
Union all
Select E.EmpName, E.EmpID, H.Level+1, H.FullyQualifiedName+'.'+E.EmpName+'.'
from @Employees E
inner join Hierarchy H on H.EmpID=E.ReportsTo
)
Select Space(Level*4) + H.EmpName
from Hierarchy H
order by H.FullyQualifiedName
Notice that I am constructing a FullyQualifiedName value for each row. This value consists of full path from root to the current person. This approach helped me in filtering the records in the final select statement where I just needed to look for a '.' + ManagerName + '.' filter. Here is a select query that extracts all subordinates for a particular manager.
Select Space(Level*4) + H.EmpName
from Hierarchy H
where CHARINDEX('.'+(Select Top(1) E.EmpName from @Employees E Where E.EmpName=@manager)+'.', H.FullyQualifiedName) > 0
order by H.FullyQualifiedName
The only issue that remains is that when the query is run for a non-parent employee, we get extra spaces in the beginning. E.g. when we run the query for “Paul”, we get an output like:
I solved it by inserted a sub-query in the space() expression like this:
Select Space((Level-(Select Top(1) Level from Hierarchy H2 Where H2.EmpName=@manager))*4) + EmpName
from Hierarchy H
where CHARINDEX('.'+(Select Top(1) E.EmpName from @Employees E Where E.EmpName=@manager)+'.', H.FullyQualifiedName) > 0
order by H.FullyQualifiedName
Instead of two subqueries, I tried to use a join but the performance using sub-queries was better so I submitted the subquery solution. Here’s the join version I am referring, it looks very nice and clean:
Select Space((H.Level-H2.Level)*4) + H.EmpName
from Hierarchy H
inner join Hierarchy H2 on CHARINDEX('.'+H2.EmpName+'.', H.FullyQualifiedName) > 0
where H2.EmpName=@manager
order by H.FullyQualifiedName
In the last, here is the complete solution:
--Populate test data
DECLARE @Employees TABLE (EmpID INT, EmpName VARCHAR(20), ReportsTo INT)
INSERT INTO @Employees(EmpID, EmpName, ReportsTo)
SELECT 1, 'Jacob', NULL UNION ALL
SELECT 2, 'Rui', NULL UNION ALL
SELECT 3, 'Jacobson', NULL UNION ALL
SELECT 4, 'Jess', 1 UNION ALL
SELECT 5, 'Steve', 1 UNION ALL
SELECT 6, 'Bob', 1 UNION ALL
SELECT 7, 'Smith', 2 UNION ALL
SELECT 8, 'Bobbey', 2 UNION ALL
SELECT 9, 'Steffi', 3 UNION ALL
SELECT 10, 'Bracha', 3 UNION ALL
SELECT 11, 'John', 5 UNION ALL
SELECT 12, 'Michael', 6 UNION ALL
SELECT 13, 'Paul', 6 UNION ALL
SELECT 14, 'Lana', 7 UNION ALL
SELECT 15, 'Johnson', 7 UNION ALL
SELECT 16, 'Mic', 8 UNION ALL
SELECT 17, 'Stev', 8 UNION ALL
SELECT 18, 'Paulson', 9 UNION ALL
SELECT 19, 'Jessica', 10
--Solution starts here
DECLARE @manager VARCHAR(20)
SELECT @manager = 'Jacob'
--CTE
;With Hierarchy(EmpName, EmpID, Level, FullyQualifiedName)
As
(
Select E.EmpName, E.EmpID, 0, Cast('.'+E.EmpName+'.' as Varchar(MAX))
From @Employees E
Where E.ReportsTo is null
Union all
Select E.EmpName, E.EmpID, H.Level+1, H.FullyQualifiedName+'.'+E.EmpName+'.'
from @Employees E
inner join Hierarchy H on H.EmpID=E.ReportsTo
)
--Result
Select Space((Level-(Select Top(1) Level from Hierarchy H2 Where H2.EmpName=@manager))*4) + EmpName
from Hierarchy H
where CHARINDEX('.'+(Select Top(1) E.EmpName from @Employees E Where E.EmpName=@manager)+'.', H.FullyQualifiedName) > 0
order by H.FullyQualifiedName
