O'Reilly logo
  • Dustin Waybright thinks this is interesting:

WITH EmpsCTE AS
(
  SELECT empid, mgrid, firstname, lastname, 0 AS distance
  FROM HR.Employees
  WHERE empid = 9

  UNION ALL

  SELECT M.empid, M.mgrid, M.firstname, M.lastname, S.distance + 1 AS distance
  FROM EmpsCTE AS S
    JOIN HR.Employees AS M
      ON S.mgrid = M.empid
)
SELECT empid, mgrid, firstname, lastname, distance
FROM EmpsCTE;

From

Cover of Training Kit (Exam 70-461): Querying Microsoft® SQL Server® 2012

Note

This query is recursive. Because the recursive (or subsequent query) makes reference to the anchor query, the recursive query will repeat until the set is empty. Because the recursive query joins mgrid to empid, each recurrence moves the manager's ID to the employeee id in the next iteration.