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?

CodePudding user response：

With Firebird 2.1 you are limited to a recursive stored procedure like this one:

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.5 and newer you could use a recursive CTE.

CodePudding user response：

You can also solve this with a recursive CTE in Firebird 2.1:

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;

https://dbfiddle.uk/VBJ3B8Ka

If you need to order siblings by name, you can add an order by name to the top-level select.