Firebird 4.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select rdb$get_context('SYSTEM', 'ENGINE_VERSION') as version
     , rdb$character_set_name
from rdb$database;


VERSION
RDB$CHARACTER_SET_NAME


4.0.1
UTF8


create table relation (
  nodeid integer,
  parentnodeid integer,
  name varchar(50)
)
execute block as
begin
  insert into relation(nodeid, parentnodeid, name) values (1, -1, 'Parent');
  insert into relation(nodeid, parentnodeid, name) values (2, 1, 'Child');
  insert into relation(nodeid, parentnodeid, name) values (3, 2, 'Child of child');
end
3 rows affected

with recursive t as (
  select nodeid, name
  from relation
  where parentnodeid = -1
  union all
  select r.nodeid, t.name || ' - ' || r.name
  from t
  inner join relation r on r.parentnodeid = t.nodeid
)
select name
from t;


NAME


Parent

Parent - Child

Parent - Child - Child of child