Querying hierarchical data and getting all relationships up the chain

Question

I have parent/child hierarchical data that looks like:

AgencyId  ParentAgencyId
6220        NULL
6221        6220
6222        6221
6223        6221
6224        6223
6219        6220
6225        NULL

I am trying to query to crawl the hierarchy to match all relationships and set a "level" similar to the below.

Parent    Child     Level
6220        6220        0
6220        6221        1
6220        6222        2
6220        6223        2
6220        6224        3
6220        6219        1
6221        6222        1
6221        6223        1
6221        6224        2
6223        6224        1
6225        6225        0

If the Agency doesn't have a parent, it would have a "level" of zero.

The code I was trying to get to do this is:

DECLARE @AgencyCount INT
DECLARE @i INT = 1
DECLARE @AgencyId INT

IF OBJECT_ID('tempdb..#tmpAgencies') > 0
DROP TABLE #tmpAgencies

CREATE TABLE #tmpAgencies (ID INT IDENTITY(1,1), AgencyId INT)
INSERT INTO #tmpAgencies SELECT AgencyId FROM dbo.Agency WHERE CreatorId = 59641 

IF OBJECT_ID('tempdb..#ChildAgencies') > 0
DROP TABLE #ChildAgencies

CREATE TABLE #ChildAgencies(AgencyId INT, ParentAgencyId INT, IsMultiAgencyOffice BIT,     LevelCount INT)  

SELECT @AgencyCount = COUNT(AgencyId) FROM #tmpAgencies         

WHILE @i < @AgencyCount

BEGIN  

    DECLARE @InsertedCount INT
    DECLARE @AgencyLevel INT = 0

    SELECT @AgencyId = AgencyId FROM #tmpAgencies AS TA WHERE ID = @i

    INSERT INTO #ChildAgencies(AgencyId, ParentAgencyId, IsMultiAgencyOffice, LevelCount)
    SELECT AgencyId, @AgencyId, IsMultiAgencyOffice, @AgencyLevel   
    FROM dbo.Agency
    WHERE AgencyId = @AgencyId

    SET @InsertedCount = @@ROWCOUNT

    WHILE @InsertedCount > 0
        BEGIN  

            SET @InsertedCount = NULL
            SET @AgencyLevel = @AgencyLevel + 1      

            INSERT INTO #ChildAgencies(AgencyId, ParentAgencyId, IsMultiAgencyOffice, LevelCount)
            SELECT AgencyId, @AgencyId, IsMultiAgencyOffice, @AgencyLevel
            FROM dbo.Agency AG
            WHERE AgencyId NOT IN (SELECT AgencyId FROM #ChildAgencies)
                AND ParentAgencyId IN (SELECT AgencyId FROM #ChildAgencies)
                AND StatusCode <> 109 /*QA-Deleted*/

            SET @InsertedCount = @@ROWCOUNT


        END  



    SET @i = @i + 1

END

I borrowed the inner-most loop from something else that does almost what I was looking for. I know loops are frowned upon. I was trying to do this originally with a recursive CTE, but couldn't get it to work. Also, I really hate the sub-queries in the where clause, but I was going to tackle that once I started getting the results I was looking for.

Thanks

Answer 1

What is usually needed in this situation is:

Parent    Child     Level
6220        6220        0
6220        6221        1
6220        6222        2
6220        6223        2
6220        6224        3
6220        6219        1
6225        6225        0

and not:

6221        6222        1
6221        6223        1
6221        6224        2
6223        6224        1

You can get the first part with a recursive CTE:

with cte as (
      select AgencyId as Parent, AgencyId as Child, 0 as level
      from Agency
      where ParentAgencyId is null
      union all
      select cte.Parent, a.AgencyId, cte.level + 1
      from cte join
           Agency a
           on a.ParentAgencyId = cte.Child
   )
select *
from cte;

Here is a SQL Fiddle showing it in action.

This version seems to produce the output you want:

with cte as (
      select AgencyId as Parent, AgencyId as Child, 0 as level
      from Agency
      where ParentAgencyId is null
      union all
      select cte.Parent, a.AgencyId, cte.level + 1
      from cte join
           Agency a
           on a.ParentAgencyId = cte.Child
      union all
      select a.ParentAgencyId, a.AgencyId, cte.level + 1
      from cte join
           Agency a
           on a.ParentAgencyId = cte.Child
   )
select distinct *
from cte;

The SQL Fiddle is here. I've never used a recursive CTE with two union all clauses.

EDIT:

This version produces the output you want. I seem to have overcomplicated the problem above (but along the way, did learn that recursive CTEs can have two union all subqueries):

 cte as (
  select AgencyId as Parent, AgencyId as Child, 0 as level, ParentAgencyId as OriginalPA
  from Agency
  union all
  select cte.Parent, a.AgencyId, cte.level + 1, OriginalPA
  from cte join
       Agency a
       on a.ParentAgencyId = cte.Child
)
select Parent, Child, Level
from cte
where level >  0 or OriginalPA is null;

You can see this working here.

Answer 2

I would just do it with a simple loop.

-- load some data into some table 


drop table tbl; 

create table tbl(id int, parentid int, level int);

insert into tbl(id, parentid) values (1, 2);
insert into tbl(id, parentid) values (2, NULL);
insert into tbl(id, parentid) values (3, 1);
insert into tbl(id, parentid) values (4, NULL);
insert into tbl(id, parentid) values (5, NULL);
insert into tbl(id, parentid) values (6, 5);


-- update the levels 

declare @done int;  
declare @lvl int;

set @done = 0;
set @lvl = 0;

update tbl set [level] = 0 where parentid is null;

set @lvl = 0;

while (@done = 0) 

BEGIN

  update t2 
  set t2.[level] = t1.[level] + 1
  from tbl t2 join tbl t1 on t1.[level] = @lvl and t2.parentid = t1.id;

  set @lvl = @lvl + 1;

  if (not (exists (select null from tbl where [level] is null)))
  begin
     set @done = 1
  end

END; 




select * From tbl ;