Hi ashaharka,
Refer below query.
SQL
DECLARE @Test AS TABLE(id INT,parent INT,EmployeeName CHAR(5))
INSERT INTO @Test VALUES(1,0,'admin')
INSERT INTO @Test VALUES(2,1,'a1')
INSERT INTO @Test VALUES(3,1,'b1')
INSERT INTO @Test VALUES(4,2,'c2')
INSERT INTO @Test VALUES(5,2,'d2')
INSERT INTO @Test VALUES(6,3,'e3')
INSERT INTO @Test VALUES(7,3,'f3')
INSERT INTO @Test VALUES(8,4,'g4')
INSERT INTO @Test VALUES(9,5,'h5')
DECLARE @Id INT
SET @Id = 2
;WITH cte AS (
SELECT a.id,a.parent,a.EmployeeName
FROM @Test a
WHERE id = @Id
UNION ALL
SELECT a.id,a.parent,a.EmployeeName
FROM @Test a
JOIN cte c ON a.parent = c.id)
SELECT id,parent,EmployeeName
FROM cte
ORDER BY id
Output
id parent EmployeeName
2 1 a1
4 2 c2
5 2 d2
8 4 g4
9 5 h5
