CODEWITHSUNDEEP
sqlsql-servert-sql
A.B.
A.B.

Reputation: 2470

Multi-Level parent-child relationship

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?

Upvotes: 3

Views: 3479

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269563

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

Upvotes: 6

Related Questions