CTE Recursion Ordered Tree

Viewed 63

I have create this SQL Fiddle with the following data

userId    userName    managerId
======    ========    =========
1         Adam        NULL
2         Brett       1
3         Chris       2
4         George      1
5         David       3
6         Elliot      5
7         Fred       5
8         Harry       4

How do I return a tree such that the data is returned in the following order:

Adam
  Brett
    Chris
      David
        Elliot
        Fred
  George
    Harry

I'm not worried about indenting and of course I can't just order by name (just in case Fred is corrected to Alfred).

This is what I've got so far:

WITH UserCTE AS (
  SELECT userId, userName, managerId, 0 AS EmpLevel
  FROM Users where managerId is null

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * 
  FROM UserCTE AS u 
  ORDER BY EmpLevel;
2 Answers
Related