How to create post-order traversal in SQL?

Question

Having a tree structure in database (id, parentId, order) where order means some value to sort per parent (it means order in parent list), how one can build full-SQL query to traverse this table in POST-ORDER?

What is post order is described on wiki, though only for binary trees - https://en.wikipedia.org/wiki/Tree_traversal

Not all of them is applyable to custom tree (for example IN-ORDER), but POST-ORDER is actully applyable:

      A
     / \
    B   C
       /|\
      D E F

output will be:

B D E F C A

The same in SQL data table:

|Id |ParentId | Order
|___|_________|______
|A  |null     |0
|B  |A        |0
|C  |A        |1
|D  |C        |0
|E  |C        |1
|F  |C        |2

I have been struggling with it quite a time, but looks like CTE doesn't allow inner ORDER BY clause (omg, why?!), so this task becomes impossible at my current level without stored procedures.

I think this is a really good question. Alas many people will down-vote this because they want several things 1) What have you tried 2) An experimental dataset 3) From my experience not many people dont know what `post-order` tree navigation is so you might want to explain that — gh9, Aug 03 '20 at 18:50
There are already many examples of this on Stack Overflow. The solution revolves around self joins and union all along with CTEs (Common Table Expressions). Here is a solution in Oracle. But the basic premise is the same. https://stackoverflow.com/questions/49939839/recursive-subquerying-with-sorting .. Try Googling on 'depth first' or 'breadth first' too for a more specific solution including sqlserver — JGFMK, Aug 04 '20 at 08:54
Depth first has multiple variants, one of them is POST-ORDER (LRN), which I failed to find. Googling just return typical PRE-ORDER solutions, but in sql server I can't specify ORDER BY in recursive CTE so it can't be done in such way. — eocron, Aug 04 '20 at 09:13

score 1 · Answer 1 · answered Aug 04 '20 at 12:03

More as a proof of concept than a usable answer, here's a CTE-based version. It uses STRING_AGG to concatenate the children of each node in order, then recursively replaces each node with its children to build the output string - this means it wouldn't work in situations where node keys are substrings of one another.

DECLARE @t TABLE 
(id CHAR(1) NOT NULL PRIMARY KEY,
 parentid CHAR(1) NULL,
 roworder TINYINT NOT NULL
)

INSERT @t (id, parentid, roworder)
VALUES('A', NULL, 0),
('B','A',0),
('C','A',1),
('D','C',0),
('E','C',1),
('F','C',2),
('G','E',0),-- two extra nodes to prove that this isn't a one-off
('H','E',1)


;WITH aggCTE
AS
(
    SELECT parentid, STRING_AGG(CONVERT(VARCHAR(MAX), id), ' ') WITHIN GROUP (ORDER BY Roworder) AS children
    FROM @t
    GROUP BY parentid

)
,recCTE
AS
(
    SELECT  a.parentid, 
            a.children,
            CAST(ISNULL(a.parentid,'') AS VARCHAR(MAX)) AS processed, --to prevent loops
            0 AS seq, --to pick the right output row
            a.children AS firstnode --to disambiguate if the data contains multiple trees
    FROM aggCTE AS a
    WHERE a.parentid IS NULL

    UNION ALL

    SELECT  a.parentid, 
            REPLACE(a.children, b.parentid, CONCAT(b.children, ' ', b.parentid)) AS children, 
            CONCAT(a.processed, b.parentid) AS processed, 
            a.seq + 1 AS seq, 
            a.firstnode
    FROM recCTE AS a
    JOIN aggCTE AS b
    ON CHARINDEX(b.parentid, a.children) > 0
    AND CHARINDEX(b.parentid, a.processed) = 0
)
,rnCTE
AS
(
    SELECT children,
            ROW_NUMBER() OVER (PARTITION BY firstnode ORDER BY seq DESC) AS rn
    FROM recCTE
)
SELECT children AS post_order_traversal
FROM rnCTE
WHERE rn = 1

Great! Most of the time Id will be 'bigint' or 'int' so I think you can convert keys to its fixed-size hex representations to prevent substring collisions! Netherless its a good answer! — eocron, Aug 04 '20 at 12:08
Wanted to mention it also won't work on big enough tables. Strings are limited even though max specified. — eocron, Aug 05 '20 at 14:30

score 0 · Answer 2 · answered Aug 04 '20 at 11:19

Found solution by simply implementing it as is:

If(OBJECT_ID('tempdb..#result') Is Not Null) Drop Table #result;
If(OBJECT_ID('tempdb..#stack') Is Not Null) Drop Table #stack;


create table #result (id int not null identity primary key, [value] bigint null);
create table #stack (id int not null identity primary key, [value] bigint null);

INSERT INTO #stack values(null) --inserting root identifiers here

WHILE EXISTS (SELECT * FROM #stack)
BEGIN
    declare @stack_id int, @stack_value bigint
    select top 1 @stack_id=id, @stack_value=value from #stack order by id desc
    delete from #stack where id=@stack_id

        INSERT INTO #stack
        -- here comes our query, which should fetch children of specified id and order it
        select tos.id 
        from inputTable as t
        where (ISNULL(@stack_value, 0) = 0 AND t.ParentId IS NULL) OR (ISNULL(@stack_value, 0) != 0 AND t.ParentId = @stack_value)
        order by t.[order] asc

    insert into #result values(@stack_value)
END

select [value] from #result order by id desc


If(OBJECT_ID('tempdb..#result') Is Not Null) Drop Table #result;
If(OBJECT_ID('tempdb..#stack') Is Not Null) Drop Table #stack;

As of now, it seems impossible with usage of CTE.

How to create post-order traversal in SQL?

2 Answers2