sql server - Combine Parent-Child Rows - TSQL -

li trying flatten/combine rows table parent-child hierarchy. i'm trying identify beginning , end of each 'link' - if a linked b, b linked c, , c linked d, want output link a d.

i'm trying best avoid using procedure loops, advice appreciated!

the original dataset , required output follows:

personid | form    | linkedform    ---------|---------|---------     1    |       |   b     1    |    b    |   c     1    |    c    |   d     1    |    d    |   null     2    |    e    |   f     2    |    f    |   g     2    |    g    |   null     2    |    h    |       2    |       |   null     3    |    j    |   null     3    |    k    |   l     3    |    l    |   null 

desired output:

personid | form    | linkedform    ---------|---------|---------     1    |       |   d     2    |    e    |   g     2    |    h    |       3    |    j    |   null     3    |    k    |   l 

each personid can have multiple links, , link can made of 1 or multiple forms.

-- use recursive cte build hierarchy -- start [linkedform] = null , work way ;with cte  (     select  *, [form] [root],             1 [level]        table1       [linkedform] null     union       select  t1.*,             [root],             [level] + 1        table1 t1     join    cte on cte.form = t1.linkedform ) -- level 1 last element, use row_number first element -- join 2 based on last , first level, have same personid , root ([linkedform] = null) select  cte.personid,         cte2.form,          cte.form    cte         join (  select  *,                          row_number() on (partition personid, [root] order level desc) rn                     cte) cte2              on cte2.rn = cte.level                  , cte2.personid = cte.personid                 , cte2.root = cte.root   cte.[level] = 1 order cte.personid, cte2.form