SQL query to select parent and child names in one row

Question

i have table in hsqldb and values like this

How do I write a query that displays like this

Answer 1

Here you go:

with recursive
n (root_id, id, title, parent) as (
  select id as root_id, id, name, parent from t
 union
  select n.root_id, t.id, t.name || '/' || n.title, t.parent
  from n
  join t on t.id = n.parent
)
select title as name from n where parent is null order by root_id

Result:

NAME      
----------
A         
A/A1      
B         
B/B1      
C         
C/C1      
A/A1/A2   
A/A1/A3   
A/A1/A3/A4
B/B1/B3   

For reference this is the data script I used to test:

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

insert into t (id, name, parent) values
  (0, 'A', null),
  (1, 'A1', 0),
  (2, 'B', null),
  (3, 'B1', 2),
  (4, 'C', null),
  (5, 'C1', 4),
  (6, 'A2', 1),
  (7, 'A3', 1),
  (8, 'A4', 7),
  (9, 'B3', 3);