This article is half-done without your Comment! *** Please share your thoughts via Comment ***
Check the below input data and expected output to prepare the report on Manager -> Employee Nth level hierarchy basis on the ManagerID column.
Input Data:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EmpID EmpName ManagerID ----------- ---------- ----------- 1 Anvesh NULL 2 Neevan NULL 3 Mukesh 1 4 Rajesh 3 5 Nupur 2 6 Roy 5 7 Martin 6 8 Manish 1 9 Eric 2 10 Purv 9 |
Expected Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
EmpName Hierarchy FullHierarchyName ---------- --------- -------------------------- Anvesh 1 Anvesh. Manish 8 Anvesh..Manish. Mukesh 3 Anvesh..Mukesh. Rajesh 4 Anvesh..Mukesh..Rajesh. Neevan 2 Neevan. Eric 9 Neevan..Eric. Purv 10 Neevan..Eric..Purv. Nupur 5 Neevan..Nupur. Roy 6 Neevan..Nupur..Roy. Martin 7 Neevan..Nupur..Roy..Martin. |
Create a table with sample data:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE Employees ( EmpID INT ,EmpName VARCHAR(10) ,ManagerID INT ) INSERT INTO Employees VALUES (1,'Anvesh',NULL) ,(2,'Neevan',NULL) ,(3,'Mukesh',1) ,(4,'Rajesh',3) ,(5,'Nupur',2) ,(6,'Roy',5) ,(7,'Martin',6) ,(8,'Manish',1) ,(9,'Eric',2) ,(10,'Purv',9) |
Solution:
|
1 2 3 4 5 6 7 8 9 10 |
;WITH CTE(EmpName , EmpId, Level,FullHierarchyName) AS ( Select E.EmpName, E.EmpID, 0 Level ,Cast(E.EmpName+'.' as Varchar(MAX)) FullHierarchyName From Employees E Where E.ManagerID IS NULL UNION ALL Select E.EmpName, E.EmpID, c.Level + 1 , c.FullHierarchyName+'.'+E.EmpName+'.' FullHierarchyName From Employees E INNER JOIN CTE c on c.EmpID = e.ManagerID ) SELECT H.EmpName ,CAST(H.EmpID AS VARCHAR(2)) Hierarchy , FullHierarchyName FROM CTE H ORDER BY H.FullHierarchyName |
Please try the different solution for this puzzle and share it via comment...
