CTE Sub-Sort

Stan Lake

I am trying to improve my CTE knowledge and am stuck on a point that I am hoping that someone can help me with. I have not found this in the forum, so if you know of a post that covers this, please point me towards it. Using the sample off the MS CTE page:

CREATE TABLE dbo.MyEmployees
(
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,
CONSTRAINT PK_EmployeeID PRIMARY KEY CLUSTERED (EmployeeID ASC)
);
-- Populate the table with values.
INSERT INTO dbo.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,  Level)
        AS
        (
        -- Anchor member definition
            SELECT e.ManagerID, e.EmployeeID, e.Title, 
                0 AS Level
            FROM dbo.MyEmployees AS e
            WHERE ManagerID IS NULL
            UNION ALL
        -- Recursive member definition
            SELECT e.ManagerID, e.EmployeeID, e.Title,
                Level + 1
            FROM dbo.MyEmployees AS e
            INNER JOIN DirectReports AS d
                ON e.ManagerID = d.EmployeeID
        )
        -- Statement that executes the CTE
        SELECT ManagerID, EmployeeID, Title,  Level
        FROM DirectReports
        order by level, managerid
        GO

Gives this, which I am pretty much understanding. ManagerID EmployeeID Title Level NULL 1 Chief Executive Officer 0 1 273 Vice President of Sales 1 273 16 Mark

Richard Deeming

Something like this should work:

WITH DirectReports As
(
SELECT
e.ManagerID,
e.EmployeeID,
e.Title,
0 AS Level,
CAST(e.EmployeeID As varchar(max)) As Path
FROM
@T As e
WHERE
ManagerID IS NULL

UNION ALL

SELECT 
    e.ManagerID, 
    e.EmployeeID, 
    e.Title,
    Level + 1,
    Path + '/' + CAST(e.EmployeeID As varchar(max))
FROM 
    @T As e
    INNER JOIN DirectReports As d
    ON e.ManagerID = d.EmployeeID

)
SELECT
ManagerID,
EmployeeID,
Title,
Level,
Path
FROM
DirectReports
ORDER BY
Path
;

Output:

ManagerID EmployeeID Title Level Path
NULL 1 Chief Executive Officer 0 1
1 273 Vice President of Sales 1 1/273
273 16 Marketing Manager 2 1/273/16
16 23 Marketing Specialist 3 1/273/16/23
273 274 North American Sales Manager 2 1/273/274
274 275 Sales Representative 3 1/273/274/275
274 276 Sales Representative 3 1/273/274/276
273 285 Pacific Sales Manager 2 1/273/285
285 286 Sales Representative 3 1/273/285/286

Alternatively, you could use the hierarchyid[^] type: Tutorial: Using the hierarchyid Data Type | Microsoft Docs[^]

"These people looked deep within my soul and assigned me a number based on the order in which I joined." - Homer

Stan Lake

Thank you. That was driving me nuts. I was very close, but kept just missing. Plus, thanks for the tip on hierarchyid. I will be digging into that, as soon as I get this working inside my design.

Mycroft Holmes

Psst the best way to thank Richard (the CTE guru) is to up vote his response. Voting arrows appear on the left of the message.

Never underestimate the power of human stupidity RAH