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.

"it ended up being quite complicated and didn't worked." - Please indicate what you tried and why it didn't work, to avoid reader repeating any mistakes. — underscore_d
– underscore_d, Commented Jan 10, 2020 at 12:42
I can't post the code online, but I made a temporary table t0, inserted all the entries without parent in it, and made a loop to create new tables, inserting all the entries where parent=t0.parent (or t(i-1) if i != 0), then another loop to join all the created table together. — Nes
– Nes, Commented Jan 10, 2020 at 12:51
"I can't post the code online"...why not? It's unlikely to be a revolutionary trade secret....especially if it isn't working. If it somehow contains sensitive names or something, then just find/replace them for this purpose. — ADyson
– ADyson, Commented Jan 10, 2020 at 12:56
Your example of a tree is very particular: it doesn't fork. What if c had an extra child g in addition to d? — The Impaler
– The Impaler, Commented Jan 10, 2020 at 12:58
but the answer about adding a FullID column wouldn't be possible for me why? — gotqn
– gotqn, Commented Jan 10, 2020 at 13:01

The Impaler · Accepted Answer · 2020-01-10 13:30:23Z

2

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);

edited Jan 10, 2020 at 13:30

answered Jan 10, 2020 at 13:12

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

jean Over a year ago

While I can find recursive CTE useful from time to time we must keep in mind recursivity in MS SQL is limited and resources hog. Depending on what OP needs he/she can try an XML based solution

Collectives™ on Stack Overflow

SQL recursive loop

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related