SQL Recursive Query and Output

Question

Using the following SQL statements to create table and insert values:

create table tb(id int , pid int , name nvarchar(10))
insert into tb values( 1 , null , 'A')
insert into tb values( 2 , null , 'B')
insert into tb values( 3 , null , 'C')
insert into tb values( 4 , 1 , 'D')
insert into tb values( 5 , 1 , 'E')
insert into tb values( 6 , 2 , 'F') 
insert into tb values( 7 , 3 , 'G')
insert into tb values( 8 , 4 , 'H')
insert into tb values( 9 , 5 , 'I')

And I want the the final output to display each line of this tree from root to leaf like this:

A-D-H
A-E-I
B-F
C-G

Is anyone know how to write SQL procedure to do this?thanks.

Answer 1

If you are using SQL Server then you can use a recursive query to solve it. Similar approach can be used in Oracle and PostgreSQL.

with rcte as
(
  select t1.id, t1.pid, cast(t1.name as nvarchar(100)) name
  from tb t1
  where not exists (select 1 from tb t2 where t2.pid = t1.id)
  union all
  select tb.id, tb.pid, cast(concat(tb.name, '-', rcte.name) as nvarchar(100)) name
  from rcte
  join tb on rcte.pid = tb.id
)
select name
from rcte
where rcte.pid is null

demo

It first finds the leaf nodes and then it traverses up to a root.