Map ChildID to root ParentID

Transact-SQL
1

I have a table that has child and parent ID as following -

ChildID, ParentID
1,1
2,1
5,1
4,5
3,2
6,6
9,6
7,9
8,7
...

I need to create a script to map each ChildID to the root ParentID to be like this -

1,1
2,1
5,1
4,1
3,1
6,6
9,6
7,6
8,6
...

It's preferred not to use recursive CTE because the feature is not supported in PDW system.

Thanks.

2

Maybe like this:

create table #yourtable(
   childid int
  ,parentid int
);

insert into #yourtable(childid,parentid)
 values (1,1)
       ,(2,1)
       ,(5,1)
       ,(4,5)
       ,(3,2)
       ,(6,6)
       ,(9,6)
       ,(7,9)
       ,(8,7)
;

alter table #yourtable
  add rootid int
;

update t1
   set t1.rootid=isnull(t2.parentid,t1.parentid)
  from #yourtable as t1
       left outer join #yourtable as t2
                    on t2.childid=t1.parentid
                   and t2.childid<>t2.parentid
;

while (@@rowcount>0)
begin
   update t1
      set t1.rootid=t2.parentid
     from #yourtable as t1
          inner join #yourtable as t2
                  on t2.childid=t1.rootid
                 and t2.childid<>t2.parentid
   ;
end;

select * from #yourtable;

drop table #yourtable;
3

Not sure where you got that notion. Please see the following link.

4

Here is the error I got -

Msg 100084, Level 16, State 1, Line 38
Recursive common table expressions are not supported in this version.

CTE is supported on APS. But recursive CTE is not.

5

Thank you. That works.