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:

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:

Where an example of my results are shown below:

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

CodePudding user response：

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;

CodePudding user response：

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.