CTE up and down -- finding path to a set of specific nodes in a parent child heirarchy

While dealing with hierarchical data, most of us have used Common Table Expressions (CTEs) to find all the children or parents of the node.

Here is how we can use the CTEs to calculate and build the path to a particular node with having to traverse the complete data set.

/* Let us create the table and fill it with demo data. */

Declare @SelectedIds table(Id int not null);

create table #ParentChild(

 Id int not null,

 ParentId int NULL,

  CONSTRAINT pk_ParentChild

  PRIMARY KEY (Id));

insert into #ParentChild values (11,null);

insert into #ParentChild values (2555,11);

insert into #ParentChild values (2666,11);

insert into #ParentChild values (2777,11);

insert into #ParentChild values (38888,2555);

insert into #ParentChild values (39999,2555);

insert into #ParentChild values (37777,2666);

insert into #ParentChild values (32222,2666);

insert into #ParentChild values (499999,38888);

insert into #ParentChild values (488888,38888);

insert into #ParentChild values ( 422222,37777);

insert into #ParentChild values (411111,37777);

/* The following CTE is used to calculate the path to all the child nodes. Starting from the parent down to the leaf*/

;with cte as (

select Id,ParentId, cast('' as varchar(500)) as AllParents from #ParentChild

where ParentId is null

union ALL

select pc.Id,pc.ParentId ,cast((cast(cte.AllParents as varchar(500))+','+(cast(pc.ParentId as varchar(500)))) as varchar(500)) as AllParents

 from #ParentChild pc join cte on pc.ParentId = cte.Id

)

select ID, ParentId, STUFF( AllParents,1,1,'') AllParents   from cte;

/* Results of the previous Query

-------------------------------

Id     ParentId      AllParents

11     NULL  

2555   11     11

2666   11     11

2777   11     11

32222  2666   1,2666

37777  2666   1,2666

411111 37777  ,2666,37777

422222 37777  ,2666,37777

38888  2555   1,2555

39999  2555   1,2555

488888 38888  ,2555,38888

499999 38888  ,2555,38888

*/

/* We need to find the complete path to these two nodes in the hierarchy without traversing the complete tree. */

insert into @SelectedIds values (422222);

insert into @SelectedIds values (488888);

/* The following CTE queries first find the path to the top root parent from the child leaf nodes.

Then the second query joins with the first one to get only the nodes that fall in the path for the leaf nodes we are interested in.

 */

;with cte as (

select * from #ParentChild

where Id in (select id from @SelectedIds)

union ALL

select pc.* from #ParentChild pc join cte on pc.Id = cte.ParentId

)

,cte2 as (

      select ID, ParentId, cast('' as varchar(500)) as AllParents

         from cte

      where ParentId is null

     

      union all

     

      select pc.Id, pc.ParentId,cast((cast(td.AllParents as varchar(500))+','+(cast(pc.ParentId as varchar(500)))) as varchar(500)) as AllParents

      from cte2 td

      inner join #ParentChild pc on pc.ParentId = td.Id

         inner join cte c on c.Id=pc.Id

)

select distinct cte2.Id,ParentId, STUFF( AllParents,1,1,'') AllParents from cte2

join @SelectedIds si on si.Id=cte2.Id

/* Results of the previous Query

-------------------------------

Id     ParentId      AllParents

422222 37777  11,2666,37777

488888 38888  11,2555,38888

*/

drop table #ParentChild