Select all hierarchy level and below SQL Server

Question

I am having a difficult time with this one. I have seen a few examples on how to obtain all child records from a self referencing table given a parent and even how to get the parents of child records.

What I am trying to do is return a record and all child records given the ID.

To put this into context - I have a corporate hierarchy. Where:

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

What I need is a procedure that (1) figures out what level the record is and (2) retrieves that record and all children records.

The idea being a Region can see all districts and reps in a district, Districts can see their reps. Reps can only see themselves.

I have 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 do I do this:

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 want to return:

3, 6, 7, 8, 9, 10

I'd appreciate any help or ideas on this.

What would be the expected result? – Felix Pamittan Apr 22 '16 at 03:19 — Felix Pamittan, Apr 22 '16 at 03:19
I updated the question to show the expected result. – JDBennett Apr 22 '16 at 03:23 — JDBennett, Apr 22 '16 at 03:23

score 10 · Accepted Answer · edited Jan 18 '21 at 12:30

10

You could do this via 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

edited Jan 18 '21 at 12:30

Community

1
1

answered Apr 22 '16 at 03:26

Felix Pamittan

31,544
7
41
67

That is perfect! Just out of curiosity - will this perform well against a large volume of records. I.e. say I have 50K + records and the Id is the corporate (top) parent. How will this hold up? – JDBennett Apr 22 '16 at 03:32
Generally, recursive CTE does not scale well on large tables. As with everything, test!! – Felix Pamittan Apr 22 '16 at 03:38

score 0 · Answer 2 · answered Apr 22 '16 at 12:35

Assuming that you're on a reasonably modern version of SQL Server, you can use the hierarchyid datatype with a little bit of elbow grease. First, the setup:

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

Next, we'll populate the 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 bog standard recursive table expression with one calculated column that represents the hierarchy. That's the hard part. Now, your procedure can 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 wrap up, all you're doing with the hierarchyid is storing the lineage for a given row so that you don't have to calculate it on the fly at select time.

Select all hierarchy level and below SQL Server

2 Answers2

Linked