sql server - Recursive Function for MS SQL -

i need write recursive function references table b , combine in field in table a.

table a:

id   name      reference 1    item  2    item b  3    item c  

table b:

id   parent_id   reference 1    1           abc 2    1           def 3    2           ghi 

expected result:

id   name      reference 1    item a     abcdef 2    item b     ghi 3    item c  

i don't see recursion here:

declare @a table     (       id int ,       name varchar(10) ,       reference varchar(100)     ) declare @b table     (       id int ,       parentid int ,       reference varchar(3)     )  insert  @a values  ( 1, 'item a', null ),         ( 2, 'item b', null ),         ( 3, 'item c', null )  insert  @b values  ( 1, 1, 'abc' ),         ( 2, 1, 'def' ),         ( 3, 2, 'ghi' )   update  set     reference = ca.data    @a         cross apply ( select                        (            select  b.reference                                    @b b                                   a.id = b.parentid                                 order id                             xml path('') ,                                     type                    ).value('.', 'varchar(max)') data                     ) ca  select  *    @a 

output:

id  name    reference 1   item  abcdef 2   item b  ghi 3   item c  null