Using recursive CTE to generate hierarchy results ordered by depth without the use of heiarchyid

Question

I would like to query hierarchy results ordered by depth first without the use of SQL's heiarchyid built in function. Essentially, I am hoping to accomplish the depth ordering without any fancy functions.

I have provided a temp table below that contains these records:

Id	p_Id	order1	name1
1	null	1	josh
2	null	2	mary
3	null	3	george
4	1	1	joe
5	1	2	jeff
6	2	1	marg
7	2	2	moore
8	2	3	max
9	3	1	gal
10	3	2	guy
11	4	1	tod
12	4	2	ava
13	9	1	ron
14	9	2	bill
15	9	100	pat

where p_Id is the id of the parent record, and order1 is essentially just the ordering of which the depth first output should be displayed. To show why my query does not fully work, I made the order1 of the last record 100 instead of say, 3. However this should not ultimately matter since 100 and 3 both come after the previous order1 value, 2. An example of a correct result table is shown below:

Id	p_Id	order1	name1	Descendants
1	null	1	josh	josh
4	1	1	joe	josh/joe
11	4	1	tod	josh/joe/tod
12	4	2	ava	josh/joe/ava
5	1	2	jeff	josh/jeff
2	null	2	mary	mary
6	2	1	marg	mary/marg
7	2	2	moore	mary/moore
8	2	3	max	mary/max
3	null	3	george	george
9	3	1	gal	george/gal
13	9	1	ron	george/gal/ron
15	9	2	bill	george/gal/bill
14	9	100	pat	george/gal/pat
10	3	2	guy	george/guy

Where an example of my results are shown below:

Id	p_Id	order1	name1	Descendants	levels
1	null	1	josh	josh	.1
4	1	1	joe	josh/joe	.1.1
11	4	1	tod	josh/joe/tod	.1.1.1
12	4	2	ava	josh/joe/ava	.1.1.2
5	1	2	jeff	josh/jeff	.1.2
2	null	2	mary	mary	.2
6	2	1	marg	mary/marg	.2.1
7	2	2	moore	mary/moore	.2.2
8	2	3	max	mary/max	.2.3
3	null	3	george	george	.3
9	3	1	gal	george/gal	.3.1
13	9	1	ron	george/gal/ron	.3.1.1
15	9	100	pat	george/gal/pat	.3.1.100
14	9	2	bill	george/gal/bill	.3.1.2
10	3	2	guy	george/guy	.3.2

where I have created a levels column that essentially concatenates the order1 values and separates them with a period. This almost returns the correct results, but due to the fact that I am ordering by this string (of numbers and periods), the levels value of .3.1.100 will come before .3.1.2 , which is not what the desired output should look like. I am sure there is a different method to return the correct depth order. See below for the code that generates a temp table, and the code that I used to generate the incorrect output that I have so far.

if object_id('tempdb..#t1') is not null drop table #t1
CREATE TABLE #t1 (Id int, p_Id int, order1 int, name1 varchar(150))


INSERT into #t1 VALUES 
   (1, null, 1, 'josh'),
   (2, null, 2, 'mary'),
   (3, null, 3, 'george'),
   (4, 1, 1, 'joe'),
   (5, 1, 2, 'jeff'),
   (6, 2, 1, 'marg'),
   (7, 2, 2, 'moore'),
   (8, 2, 3, 'max'),
   (9, 3, 1, 'gal'),
   (10, 3, 2, 'guy'),
   (11, 4, 1, 'tod'),
   (12, 4, 2, 'ava'),
   (13, 9, 1, 'ron'),
   (14, 9, 2, 'bill'),
   (100, 9, 100, 'pat');

select * from #t1

-- Looking to generate heiarchy results ordered by depth --
; with structure as (

    -- Non-recursive term.
    -- Select the records where p_Id is null

    select  p.Id,
            p.p_Id,
            p.order1,
            p.name1,
            cast(p.name1 as varchar(64)) as Descendants,
            cast(concat('.', p.order1) as varchar(150)) as levels 
        from #t1 p 
        where p.p_Id is null

        union all

        -- Recursive term.
        -- Treat the records from previous iteration as parents.
        -- Stop when none of the current records have any further sub records.

        select  c.Id,
                c.p_Id,
                c.order1,
                c.name1,
                cast(concat(p.Descendants, '/', c.name1) as varchar(64)) as Descendants,
                cast(concat(p.levels, '.', c.order1) as varchar(150)) as levels
            from #t1 c                               -- c being the 'child' records
                inner join structure p                   -- p being the 'parent' records
                    on c.p_Id = p.Id

)

select  *
    from structure
    order by replace(levels, '.', '') asc

score 2 · Accepted Answer · answered Dec 17 '22 at 00:19

Take II. As pointed out by OP my original answer fails for more than 10 children. So what we can do (OP's suggestion) is pad the values out with zeros to a constant length. But what length? We need to take the largest number of children under a node and add this to the largest value or order, so for the example provided this is 100 + 3, and then take the length of that (3) and pad every order with zeros to 3 digits long. This means we will always be ordering as desired.

declare @PadLength int = 0;

select @PadLength = max(children)
from (
    select len(convert(varchar(12),max(order1)+count(*))) children
    from #t1
    group by p_Id
) x;

-- Looking to generate heiarchy results ordered by depth --
with structure as (
    -- Non-recursive term
    -- Select the records where p_Id is null
    select
        p.Id [Id]
        , p.p_Id [ParentId]
        , p.order1 [OrderBy]
        , p.name1 [Name]
        , cast(p.name1 as varchar(64)) Descendants
        , concat('.', right(replicate('0',@Padlength) + convert(varchar(12),p.order1), @PadLength)) Levels
    from #t1 p 
    where p.p_Id is null

    union all

    -- Recursive term
    -- Treat the records from previous iteration as parents.
    -- Stop when none of the current records have any further sub records.
    select
        c.Id,
        c.p_Id,
        c.order1,
        c.name1,
        cast(concat(p.Descendants, '/', c.name1) as varchar(64)),
        concat(p.levels, '.', right(replicate('0',@Padlength) + convert(varchar(12),c.order1), @PadLength))
    from #t1 c -- c being the 'child' records
    inner join structure p on c.p_Id = p.Id -- p being the 'parent' records
)
select *
from structure
order by replace(levels, '.', '') asc;

Dale K · Answer 2 · 2022-12-17T00:16:32.363

Note: This answer fails in the case when there are more than 10 children under a particular node. Leaving for interest.

So this issue you have run into is that you are ordering by a string not a number. So the string 100 comes before the string 2. But you need to order by a string to take care of the hierarchy, so one solution is to replace order1 with row_number() based on the order1 column while its still a number and use the row_number() to build your ordering string.

So you replace:

cast(concat(p.levels, '.', c.order1) as varchar(150)) as levels

with

cast(concat(p.levels, '.', row_number() over (order by c.Order1)) as varchar(150))

giving a full query of

with structure as (

    -- Non-recursive term.
    -- Select the records where p_Id is null

    select  p.Id,
            p.p_Id,
            p.order1,
            p.name1,
            cast(p.name1 as varchar(64)) as Descendants,
            cast(concat('.', p.order1) as varchar(150)) as levels 
        from #t1 p 
        where p.p_Id is null

        union all

        -- Recursive term.
        -- Treat the records from previous iteration as parents.
        -- Stop when none of the current records have any further sub records.

        select  c.Id,
                c.p_Id,
                c.order1,
                c.name1,
                cast(concat(p.Descendants, '/', c.name1) as varchar(64)) as Descendants,
                cast(concat(p.levels, '.', row_number() over (order by c.Order1)) as varchar(150))
            from #t1 c                               -- c being the 'child' records
                inner join structure p                   -- p being the 'parent' records
                    on c.p_Id = p.Id

)
select  *
    from structure
    order by replace(levels, '.', '') asc;

Which returns the desired results.

Note: good question, well written.

Thanks Dale. That is a good step towards the correct answer (for a bigger dataset and not this test case). The reason I dont believe this will quite work for the bigger dataset I am using is because there can be more than say 10 child records. If this is the case, then the row_number() will still rank correctly, but you might end up with a order1 value that is .1.10, and another that is .1.2. So 1.10.1 would come before 1.2.1 in this string, which is not quite correct again. Do you see this as well? — DizzleBeans, Dec 17 '22 at 00:00
I think what would solve it is basically padding the value with zeros so that the order1 value would be 1.02, which would come before 1.10. Does this seem like the right way to further solve this problem? — DizzleBeans, Dec 17 '22 at 00:00
Yeah. Is there a way to 'dynamically' pad the value such that it is exactly padded with enough zeros to equal the length of the largest value? So if there were 10 children then only 1 zero is needed to pad 02, but if there is 10,000 children, then 4 zeros are needed to pad 00002. — DizzleBeans, Dec 17 '22 at 00:09

Using recursive CTE to generate hierarchy results ordered by depth without the use of heiarchyid

2 Answers2