Concatenate node names recursively

Question

I'm using Firebird 2.1 and I have the following hierarchical table:

NodeID, ParentNodeID, Name

ParentNodeID = -1 for root nodes.

For example:

1, -1, Parent
2, 1, Child
3, 2, Child of child

I'm looking for a recursive query (or stored procedure) to output a concatenation the following way:

Parent
Parent - Child
Parent - Child - Child of child

Siblings should be sorted in alphabetic order. How do I do this?

Answer 1

You can do it with a stored procedure:

create procedure tree (root integer) returns (result varchar(1000)) as
  declare id integer;
  declare n varchar(30);
  declare childs varchar(1000);
begin
  for select NodeId, Name from t where ParentNodeId = :root order by Name into :id, :result do
   begin
    suspend;
    n = result;
    for select result from tree(:id) into :childs do
      begin
        result = n || ' - ' || childs;
        suspend;
      end
   end
end

https://dbfiddle.uk/_fY5xZS6

In Firebird 2.1 and newer you could also use a recursive CTE.