3

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;
openshac
  • 4,966
  • 5
  • 46
  • 77

2 Answers2

3

You need to do this by getting the complete path to each person, and then sorting by that:

WITH UserCTE AS (
      SELECT userId, userName, managerId, 0 AS EmpLevel,
             CONVERT(VARCHAR(MAX), '/' + userName) as path
      FROM Users 
      WHERE managerId is null
      UNION ALL
      SELECT usr.userId, usr.userName, usr.managerId, mgr.[EmpLevel]+1,
             CONVERT(VARCHAR(MAX), mgr.path + '/' + usr.userName)
      FROM Users usr INNER JOIN
           UserCTE mgr
           ON usr.managerId = mgr.userId 
      WHERE usr.managerId IS NOT NULL  -- this is unnecessary
     )
SELECT * 
FROM UserCTE AS u 
ORDER BY path;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • The query seems to create a 'Adam//Brett'. I think the first part of the query needs to be CONVERT(VARCHAR(MAX), userName) as path. – openshac Nov 28 '17 at 13:24
  • @openshac . . . I don't think that affects the ordering, but i moved the `/` to where I really intended it. – Gordon Linoff Nov 28 '17 at 13:26
2

How about using sql server hierarchyid to order these in the correct order: SQL Fiddle

MS SQL Server 2014 Schema Setup:

CREATE TABLE [dbo].[Users](
    [userId] [int] ,
    [userName] [varchar](50) ,
    [managerId] [int] ,
   )

INSERT INTO dbo.Users
    ([userId], [userName], [managerId])
VALUES
(1,'Adam',NULL),
(2,'Brett',1),
(3,'Chris',2),
(4,'George',1),
(5,'David',3),
(6,'Elliot',5),
(7,'Frank',5),
(8,'Harry',4)

Query 1:

WITH UserCTE AS (
  SELECT userId, userName, managerId, hierarchyid::GetRoot() AS EmpLevel
  FROM Users where managerId is null

  UNION ALL

  SELECT usr.userId, usr.userName, usr.managerId
         , cast(mgr.EmpLevel.ToString() + cast(usr.userId As varchar(30)) + '/' as hierarchyid) as EmpLevel
  FROM Users AS usr
    INNER JOIN UserCTE AS mgr
      ON usr.managerId = mgr.userId where usr.managerId IS NOT NULL
)
SELECT * , EmpLevel.ToString()
  FROM UserCTE AS u 
  ORDER BY EmpLevel

Results:

| userId | userName | managerId | EmpLevel |           |
|--------|----------|-----------|----------|-----------|
|      1 |     Adam |    (null) |          |         / |
|      2 |    Brett |         1 |     aA== |       /2/ |
|      3 |    Chris |         2 |     a8A= |     /2/3/ |
|      5 |    David |         3 |     a+M= |   /2/3/5/ |
|      6 |   Elliot |         5 |     a+OU | /2/3/5/6/ |
|      7 |    Frank |         5 |     a+Oc | /2/3/5/7/ |
|      4 |   George |         1 |     hA== |       /4/ |
|      8 |    Harry |         4 |     hog= |     /4/8/ |
Steve Ford
  • 7,433
  • 19
  • 40