3

I have a table like this:

enter image description here

I want to output the hierarchy like this:

a - c - x1
a - c - x2
a - d - y1
a - d - y2
b - e - z
b - f - q

I searched a little for CTE examples, but they list only a (2,2)-combination of the hierarchy. How can I achieve this result without depending on the parent-child depth?

A.B.
  • 2,374
  • 3
  • 24
  • 40
  • 1
    Will there always be at most 3 levels? – George Menoutis Jul 04 '18 at 11:45
  • As a rule of thumb if it got a fixed depth a self join is ok, for variable depth you must use a recursive CTE. SQL is not great when it comes to this kind of hierarchical problem (and has limitations on recursivity) but you can easily use XML in such situations (even if performance is not that great) – jean Jul 04 '18 at 11:54

1 Answers1

6

You want to use a recursive CTE.

The following gets all paths:

with cte as (
      select cast(child as varchar(max)) as path, child, 1 as lev
      from t
      where parent is null
      union all
      select cast(cte.path + ' - ' + t.child as varchar(max)), t.child, lev + 1
      from cte join
           t
           on cte.child = t.parent
     )
select *
from cte;

If you just want the paths to terminal leafs:

select *
from cte
where not exists (select 1
                  from t
                  where t.parent = cte.child
                 );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Recursion...nice! Gordon, will this go into infinite recursion if a cycle exists? Maybe one should be careful to set maxrecursion to a not huge number as a precaution? – George Menoutis Jul 04 '18 at 11:52
  • @GeorgeMenoutis by default, a CTE has a maximum recursion of 100. You would have to explicitly state `OPTION (MAXRECURSION 0)` to cause it to recurse infinitely. – Thom A Jul 04 '18 at 11:53
  • That's the best way to get historical parents for every child with on query i thought i had to use While for this solution before it – Omid Farvid Jan 13 '19 at 07:10