Ejemplo Consulta Recursiva SQL

declare  @MyEmployees TABLE
(
EmployeeID smallint NOT NULL,
FirstName nvarchar(30)  NOT NULL,
LastName  nvarchar(40) NOT NULL,
Title nvarchar(50) NOT NULL,
DeptID smallint NOT NULL,
ManagerID int NULL

)

INSERT INTO @MyEmployees VALUES
 (1, N'Ken', N'Sánchez', N'Chief Executive Officer',16,NULL)
,(273, N'Brian', N'Welcker', N'Vice President of Sales',3,1)
,(274, N'Stephen', N'Jiang', N'North American Sales Manager',3,273)
,(275, N'Michael', N'Blythe', N'Sales Representative',3,274)
,(276, N'Linda', N'Mitchell', N'Sales Representative',3,274)
,(285, N'Syed', N'Abbas', N'Pacific Sales Manager',3,273)
,(286, N'Lynn', N'Tsoflias', N'Sales Representative',3,285)
,(16,  N'David',N'Bradley', N'Marketing Manager', 4, 273)
,(23,  N'Mary', N'Gibson', N'Marketing Specialist', 4, 16);


WITH DirectReports (ManagerID, EmployeeID, Title, Nivel)
AS
(
    SELECT e.ManagerID, e.EmployeeID, e.Title,
        0 AS Level
    FROM @MyEmployees AS e
    WHERE ManagerID IS NULL
   
    Union all
   
     SELECT e.ManagerID, e.EmployeeID, e.Title,
        Nivel + 1
    FROM @MyEmployees AS e
INNER JOIN DirectReports AS d
ON e.ManagerID = d.EmployeeID
)


Select * from DirectReports


Resultado