SQL Server - Concatenate all child parent relationships into a single string

Question

The problem: I have a table that has family trees that has parents and child elements. What I need to return is the family tree for each child. I cant figure out how to do this with a cte or alike

Table:

ID	Name	Parent
1	Child1	2
2	parent1	3
3	Grandparent1	null
4	Child2	5
5	parent2	null
6	Child3	null

Answer 1

This is basically a recursive CTE. The tricky part is removing the current name from the family list.

The following approach uses the recursive CTE to generate all family members and then returns the current name. If the names can overlap significantly (such as "grandchild1" and "child1"), then it might need to be tweaked. But it works for your example:

with cte as (
      select id, name, convert(varchar(max), ',' + name) as family, 1 as lev
      from t
      where parent is null
      union all
      select t.id, t.name, concat(cte.family, ',', t.name), lev + 1
      from cte join
           t
           on t.parent = cte.id
     )
select id, name,
       stuff(nullif(replace(family, ',' + name, ''), ''), 1, 1, '') as familh
from (select cte.*, max(lev) over (partition by id) as max_lev
      from cte
     ) cte
where lev = max_lev;

Here is a little db<>fiddle.