Return all intermediate members of a recursive CTE?

Question

Return all intermediate members of a recursive CTE?

SSC Guru

Points: 63694

August 11, 2026 at 7:29 pm

I wrote a recursive CTE to determine employee levels... but what if I wanted to get a list of everybody in a "tree" that reports to a given employee? Here's the query...

;WITH cteHierarchy(EmployeeName, ManagerName, lvl)
AS (
/* anchor member */SELECT EmployeeName
,ManagerName
,1
FROM dbo.OfficeSpace
WHERE ManagerName IS NULL

UNION ALL

/* note the join to the CTE in the recursive member! */SELECT o.EmployeeName
,o.ManagerName
,h.lvl + 1
FROM dbo.OfficeSpace o
INNER JOIN cteHierarchy h ON h.EmployeeName = o.ManagerName
)
/* 1 = top of the hierarchy! */SELECT ManagerName, EmployeeName, lvl
FROM cteHierarchy

and because I'm a huge fan of movies like Memento (told backwards), here's my data:

use tempdb;
go

CREATE TABLE [dbo].[OfficeSpace](
[EmployeeName] [nvarchar](50) NOT NULL,
[ManagerName] [nvarchar](50) NULL,
 CONSTRAINT [PK_OfficeSpace (3)] PRIMARY KEY CLUSTERED 
(
[EmployeeName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

INSERT INTO OfficeSpace(EmployeeName, ManagerName)
VALUES 
('Alan B. Peterson','Nathan R. Ross'),
('Alice L. Munroe','Nathan C. Carter'),
('Anne Martinez','Wendy L. Hargrove'),
('Bill Lumbergh',NULL),
('Bob Porter','Bill Lumbergh'),
('Bob Slydell','Bill Lumbergh'),
('Bobbie K. Jenkins','Alan B. Peterson'),
('Cheryl T. Ackerman','Peter Gibbons'),
('Derek P. Phillips','Tom Smykowski'),
('Dom Portwood','Linda M. Grayson'),
('Fred Wilkinson','Wendy L. Hargrove'),
('Greg S. Torres','Nathan C. Carter'),
('Linda M. Grayson','Bill Lumbergh'),
('Lydia Bennett','Nathan C. Carter'),
('Maria D. Sanchez','Alan B. Peterson'),
('Michael Bolton','Samir Nagheenanajar'),
('Milton Waddams','Tom Smykowski'),
('Nathan C. Carter','Dom Portwood'),
('Nathan R. Ross','Linda M. Grayson'),
('Peggy Carlson','Wendy L. Hargrove'),
('Peter Gibbons','Dom Portwood'),
('Samir Nagheenanajar','Dom Portwood'),
('Sarah J. Greene','Derek P. Phillips'),
('Tom Smykowski','Linda M. Grayson'),
('Wendy L. Hargrove','Linda M. Grayson');

/* I can determine who manages who... but how do I determine all members in a given "branch" (say all subordinates of Dom Portwood?) */

So something like...

Hargrove [works for] Grayson [works for] Lumbergh [works for] NULL (top of heap) ?

Viewing 3 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004775 More actions · Answer 1

To quote Brent Ozar, "What is the problem that you're trying to solve"? Why do you need such a text based "tree and what are you going to do when you have multiple sub-trees?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Steve Collins SSCrazy Points: 2278 More actions · Answer 2

Hey there, got answers to Jeff's questions?

"what if I wanted to get a list of everybody in a "tree" that reports to a given employee?" and "how do I determine all members in a given "branch" (say all subordinates of Dom Portwood?)"

You could use your existing query if you adjust the CTE to point to a different anchor member. For Dom Portwood do you want his manager too or only the employees who report to him? If you want his manager too you could make him the anchor Employee in the predicate:

WHERE EmployeeName='Dom Portwood'

If you only want the people that report to Dom then you could make the him the anchor Manager

WHERE ManagerName='Dom Portwood'

With ManagerName='Dom Portwood' the result:

ManagerName        EmployeeName        lvl
Dom Portwood        Nathan C. Carter1
Dom Portwood        Peter Gibbons        1
Dom Portwood        Samir Nagheenanajar1
Samir NagheenanajarMichael Bolton        2
Peter Gibbons        Cheryl T. Ackerman2
Nathan C. CarterAlice L. Munroe        2
Nathan C. CarterGreg S. Torres        2
Nathan C. CarterLydia Bennett        2

Maybe the real question is how to persist the data such that answering further questions doesn't require repeating the recursions? If so, imo, you have 3 options. Option 1 (the Jeff method) is nested sets where you enumerate the reporting hierarchy by projecting it onto a line and assigning leftmost and rightmost boundaries (or "bowers"). Option 2 (the Steve method) is storing resolved intermediate reporting hierarchies as JSON. Option 3 (the Microsoft method) is using SQL Server's HIERARCHYID data type. Which way is appropriate? It depends on how you answer Jeff's questions. Why do you need this? Why is the PK possibly subject to mutation? What happens when 'Dom Portwood' marries 'Cheryl T. Ackerman' and she changes her name to 'Cheryl A. Portwood'?

Aus dem Paradies, das Cantor uns geschaffen, soll uns niemand vertreiben können