Select all hierarchy levels and below SQL Server

Question

I have a difficult time with this. I saw several examples of how to get all the child records from the self-reference table provided to the parents, and even how to get the parents of the child records.

What I'm trying to do is return the record and all child records, given an identifier.

To put this in context, I have a corporate hierarchy. Where:

#Role Level# -------------------- Corporate 0 Region 1 District 2 Rep 3 

I need a procedure that (1) determines the record level and (2) retrieves this record and all records for children.

An idea that is a Region can see all the districts and representatives in the district, Regions can see its representatives. Representatives can only see themselves.

I have a table:

 ID ParentId Name ------------------------------------------------------- 1 Null Corporate HQ 2 1 South Region 3 1 North Region 4 1 East Region 5 1 West Region 6 3 Chicago District 7 3 Milwaukee District 8 3 Minneapolis District 9 6 Gold Coast Dealer 10 6 Blue Island Dealer 

How to do it:

 CREATE PROCEDURE GetPositions @id int AS BEGIN --What is the most efficient way to do this-- END GO 

For example, the expected result for @id = 3, I would like to return:

 3, 6, 7, 8, 9, 10 

I would be grateful for any help or ideas on this.

Answer 1

You can do this with a recursive CTE:

 DECLARE @id INT = 3; WITH rCTE AS( SELECT *, 0 AS Level FROM tbl WHERE Id = @id UNION ALL SELECT t.*, r.Level + 1 AS Level FROM tbl t INNER JOIN rCTE r ON t.ParentId = r.ID ) SELECT * FROM rCTE OPTION(MAXRECURSION 0); 

ONLINE DEMO

Answer 2

Assuming you are using a fairly modern version of SQL Server, you can use a hierarchyid data type with a little elbow lubrication. First, setting:

 alter table [dbo].[yourTable] add [path] hierarchyid null; 

Then we write a new column:

 with cte as ( select *, cast(concat('/', ID, '/') as varchar(max)) as [path] from [dbo].[yourTable] where [ParentID] is null union all select child.*, cast(concat(parent.path, child.ID, '/') as varchar(max)) as [path] from [dbo].[yourTable] as child join cte as parent on child.ParentID = parent.ID ) update t set path = c.path from [dbo].[yourTable] as t join cte as c on t.ID = c.ID; 

This is just a standard swamp table recursive expression with one computed column representing a hierarchy. This is the hard part. Your procedure may now look something like this:

 create procedure dbo.GetPositions ( @id int ) as begin declare @h hierarchyid set @h = (select Path from [dbo].[yourTable] where ID = @id); select ID, ParentID, Name from [dbo].[yourTable] where Path.IsDescendentOf(@h) = 1; end 

So, to complete, all you do with the hierarchy is to save the line for a given row so that you don’t have to calculate it on the fly at a specific time.