Return all intermediate members of a recursive CTE?

• pietlinden

  SSC Guru

  Points: 63691

  More actions

  August 11, 2026 at 7:29 pm

  #4927168

  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) ?