How to retrieve sorted hierarchical data form SQL Server

Question

I have seen many examples of hierarchical ctes, but I wasn't able to get my sorting right yet.

To describe my problem, assume this Task table:

TaskId    ParentTaskId    Label
a10       null            10
a20       null            11
a30       a20             18
a50       a30             5
a40       a20             15
a60       null            12

The output of my query should be sorted by Label and the children's labels, like so:

Sequence   TaskId       Label
1          a10          10
2          a20          11
3            a40        15
4            a30        18
5              a50       5
6          a60          12

I added indentation to make it easier for you to notice the grouping. a40 and a30 are children of a20, and are ordered based on the label.

Please help. thanks!

Answer 1

Here's the answer:

drop table if exists #t
go

select
    *
into
    #t
from
(
    values 
        ('a10', null, '10'),
        ('a20', null, '11'),
        ('a30', 'a20', '18'),
        ('a50', 'a30',  '5'),
        ('a40', 'a20', '15'),
        ('a60', null, '12')
) as T(TaskId, ParentTaskId, Label)
;

with cte as
(
    select 
        l = 0, 
        p = cast('/' + Label as nvarchar(max)), 
        * 
    from 
        #t where ParentTaskId is null

    union all

    select 
        l = p.l + 1, 
        p = p.p + '/' + c.Label, 
        c.* 
    from 
        #t c 
    inner join 
        cte p on c.ParentTaskId = p.TaskId
)
select
    *
from
    cte
order by p, Label

You need to create a path from the root of your task to the current task and then use it to sort the final result. On small dataset the above query will perform fine. On bigger (hundreds of thousands) I recommend to take a look at hierarchyid data type:

https://learn.microsoft.com/en-us/sql/t-sql/data-types/hierarchyid-data-type-method-reference?view=sql-server-2017