Is there a way to retrieve a parent child tree from a single table, with multiple roots and a child_order column?

Question

I have a table that looks like this:

CREATE TABLE tbl 
(
    id int,
    parent_id int,
    child_order int,
    name varchar(20)
)

Take this as a sample dataset:

1   NULL    0   BtVS
2   NULL    1   Star Trek
3   1       0   Buffy
4   1       1   Willow
5   1       2   Xander
6   1       3   Giles
7   2       0   Kirk
8   2       1   Spock
9   2       2   McCoy
10  2       3   Uhura
11  3       0   Strength
12  3       1   Speed
13  3       2   Dreams
14  4       0   Tech
15  4       1   Magic
16  4       2   Dreams
17  5       0   Heart
18  5       1   Humor
19  5       2   Loyalty
20  6       0   Wisdom
21  6       1   Training
22  6       2   Guidance
23  7       0   Leadership
24  7       1   Independence
25  7       2   Courage
26  8       0   Logic
27  8       1   Strength
28  8       2   Nerve Pinch
29  9       0   Healing
30  9       1   Sarcasm
31  9       2   Friendship
32  10      0   Communications
33  10      1   Languages
34  10      2   Music

I want it to output like this:

1   1   BtVS
3   2       Buffy
11  3           Strength
12  3           Speed
13  3           Dreams
4   2       Willow
14  3           Tech
15  3           Magic
16  3           Dreams
5   2       Xander
17  3           Heart
18  3           Humor
19  3           Loyalty
6   2       Giles
20  3           Wisdom
21  3           Training
22  3           Guidance
2   1   Star Trek
7   2       Kirk
23  3           Leadership
24  3           Independence
25  3           Courage
8   2       Spock
26  3           Logic
27  3           Strength
28  3           Nerve Pinch
9   2       McCoy
29  3           Healing
30  3           Sarcasm
31  3           Friendship
10  2       Uhura
32  3           Communications
33  3           Languages
34  3           Music

Giving me the id column, the report level, and the name column. But in order, like this. The child_order column may be discontinuous. Meaning that there might be 4 children of a single parent with child_orders of 0, 1, 5 and 9. It's only their relative order that matters. And there can be more than 10 children, so I can't rely on the number of digits in the child_order.

I've tried working with CTEs, but I haven't found a way to make it work with multiple roots, and I haven't found a way to make it work with the child_order column, because ORDER BY is not valid inside a CTE.

Is there a way to accomplish this that's reasonable? I've thought about using powers of 10 for each report level, and adding the hierarchy to get a sort value, but like I said, you can have two digit child_orders, which would mess that up.

Answer 1

Seems like you can achieve this with an rCTE, as I mentioned. To get the ordering, you can create a hierarchyid (like) string, and then CONVERT that to said data type in the ORDER BY:

WITH rCTE AS(
    SELECT t.id,
           t.name,
           t.child_order,
           0 AS Level,
           CONVERT(varchar(8000),CONCAT('/',t.id,'/')) AS hid --or use child_order
    FROM dbo.tbl t
    WHERE t.parent_id IS NULL
    UNION ALL
    SELECT t.id,
           t.name,
           t.child_order,
           r.Level + 1,
           CONVERT(varchar(8000),CONCAT(r.hid,t.id,'/')) --or use child_order
    FROM dbo.tbl t
         JOIN rCTE r ON t.parent_id = r.id)
SELECT r.id,
       r.child_order,
       CONCAT(REPLICATE(CHAR(9),r.Level),r.name) AS name
FROM rCTE r
ORDER BY CONVERT(hierarchyid,r.hid);