Parent Child relation in SQL Server

Question

I have a table with this structure:

ParentProjectID  ChildProjectID
------------------------------    
     101             102
     102             103
     103             104
     201             202
     202             203

Let me explain the scenario, when we renew a project we treat this as a new project and enter it under its parent project.

Like 102 is child project of its parent 102 and child 103's parent is 102 and so on.

Now, my question is to find out the grand parent, parent and child.

Like in above case 101 is grand parent of 102,103 and 104. And 102 is parent of 103 and 104.

So, I want my result as:

(If I pass 101 as parameter of ParentProjectID)

ParentProjectID  ChildProjectID
      101             102
      101             103
      101             104

Any help will be appreciated.

Answer 1

You can use recursive common table expression:

create procedure usp_Descendants
(
  @ParentProjectID int
)
as
begin
    ;with cte as (
         select
             T.ChildProjectID
         from Table1 as T
         where T.ParentProjectID = @ParentProjectID
         union all
         select
             T.ChildProjectID
         from cte as c
             inner join Table1 as T on T.ParentProjectID = c.ChildProjectID
    )
    select
        @ParentProjectID, c.ChildProjectID
    from cte as c
end

exec usp_Descendants @ParentProjectID = 101;
-----------
101     102
101     103
101     104

exec usp_Descendants @ParentProjectID = 101;
-----------
102     103
102     104

sql fiddle demo