IF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee_Tree]') AND type in (N'U'))
DROP TABLE [dbo].[Employee_Tree]
CREATE TABLE [dbo].[Employee_Tree](
[Employee_NM] [nvarchar](50) NULL,
[Employee_ID] [int] NOT NULL,
[ReportsTo] [int] NULL)
INSERT INTO Employee_Tree VALUES('Richard', 1, NULL)
INSERT INTO Employee_Tree VALUES('Stephen', 2, 1)
INSERT INTO Employee_Tree VALUES('Clemens', 3, 2)
INSERT INTO Employee_Tree VALUES('Malek', 4, 2)
INSERT INTO Employee_Tree VALUES('Goksin', 5, 4)
INSERT INTO Employee_Tree VALUES('Kimberly', 6, 1)
INSERT INTO Employee_Tree VALUES('Ramesh', 7, 5)
;WITH SimpleRecursive ( Employee_NM, Employee_ID, ReportsTo )
AS ( SELECT Employee_NM ,
Employee_ID ,
ReportsTo
FROM Employee_Tree
WHERE Employee_ID = 4
UNION ALL
SELECT p.Employee_NM ,
p.Employee_ID ,
p.ReportsTo
FROM Employee_Tree p
INNER JOIN SimpleRecursive A ON A.ReportsTo = p.Employee_ID
)
SELECT * FROM SimpleRecursive
