SQL : Hierarchy chain (reverse path)

SQL : Hierarchy chain (reverse path)

Hierarchy chain (reverse path)

Create Table

 CREATE TABLE [dbo].[employee](
 [EmpName] [varchar](50) NULL,
 [EmpID] [int] NULL,
 [ManagerID] [int] NULL
) ON [PRIMARY]
GO

Insert Data

INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Saurav', 1, 0)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Dravid', 2, 1)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Kapila', 3, 2)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Pranil', 4, 2)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Rohini', 5, 4)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Peeter', 6, 0)
INSERT [dbo].[employee] ([EmpName], [EmpID], [ManagerID]) VALUES (N'Keveen', 7, 6)
GO

Create a hierarchy chain (reverse path) for specific node in a tree

;with CTE(EmpID, empName, ManagerID)
AS
(
 SELECT EmpID, empName, ManagerID 
 FROM Employee F
 WHERE EmpID = 5
 UNION ALL
 SELECT F.EmpID, F.empName, F.ManagerID  
 FROM Employee F
 INNER JOIN CTE FH ON FH.ManagerID = F.EmpID  
)
SELECT * FROM CTE

Create a hierarchy chain for specific node in a tree with all child levels

WITH empCTE
AS ( SELECT EmpID, empName, ManagerID
FROM Employee
WHERE empid = 1 -- and ManagerID = 0 
UNION ALL
SELECT e.EmpID, e.empName, e.ManagerID
FROM Employee e
INNER JOIN empCTE ON e.ManagerID = empCTE.EmpID
WHERE e.ManagerID != 0)
SELECT *
FROM empCTE

Hope this will help you and save your time.

Enjoy !!!

:)