SQL recursive loop

Question

I'm in a situation quite similar to MySql sql recursive loop, but the answer about adding a FullID column wouldn't be possible for me.

I've a table TREE of elements with a tree hierarchy :

ID NAME PARENT
 1    a  null
 2    b  null
 3    c     1
 4    d     3
 5    e     2
 6    f     4
 7    g     3

How could I make a query that would return a result like that :

a - c - d - f
a - c - g
b - e

I tried with WHILE loops, creating queries and temporary tables dynamically, but it ended up being quite complicated and didn't worked.

Answer 1

You can use a recursive CTE to get all branches. The generic solution looks like:

with
b as (
  select id, parent, cast(concat('', name) as varchar(255)) as branch 
  from t 
  where id not in (select parent from t where parent is not null)
union all
  select t.id, t.parent, cast(concat(t.name, ' - ', b.branch) as varchar(255))
  from b
  join t on t.id = b.parent
)
select id, branch from b where parent is null

Result:

branch       
-------------
a - c - g    
a - c - d - f
b - e        

For reference, this is the data script I used:

create table t (
  id int,
  name varchar(6),
  parent int
);

insert into t (id, name, parent) values 
  (1, 'a', null),
  (2, 'b', null),
  (3, 'c', 1),
  (4, 'd', 3),
  (5, 'e', 2),
  (6, 'f', 4),
  (7, 'g', 3);