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…

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…

Map ChildID to root ParentID

Transact-SQL

JeffModen April 2, 2017, 1:30am 3

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