recursion - Ordering hierarchy from recursive query results in SQL 2005

Question

I've got a 'Task' table with the following columns (the TaskOrder is for ordering the children within the scope of the parent, not the entire table):

TaskId
ParentTaskId
TaskName
TaskOrder

I've got this CTE query to return all the rows:

with tasks (TaskId, ParentTaskId, [Name]) as
(
    select parentTasks.TaskId,
           parentTasks.ParentTaskId,
           parentTasks.[Name]
    from   Task parentTasks
    where  ParentTaskId is null

    union all

    select childTasks.TaskId,
           childTasks.ParentTaskId,
           childTasks.[Name]
    from   Task childTasks
    join   tasks
    on     childTasks.ParentTaskId = tasks.TaskId
)

select * from tasks

This query returns all the tasks ordered by their level as you'd expect. How can I change it to order the results into their hierarchy order as below?

- Task 1
-- Task 1 Subtask 1
-- Task 1 Subtask 2
- Task 2
- Task 3

Thanks.

Edit: The answer should work with an unlimited numbr of levels.

Answer 1

One way you could do this is to add a hierarchy column that has all previous IDs in a list:

with tasks (TaskId, ParentTaskId, [Name], TaskIdList) as
(
    select parentTasks.TaskId,
           parentTasks.ParentTaskId,
           parentTasks.[Name],
           parentTasks.TaskId
    from   Task parentTasks
    where  ParentTaskId is null

    union all

    select childTasks.TaskId,
           childTasks.ParentTaskId,
           childTasks.[Name],
           tasks.TaskIdList + '.' + childTasks.TaskId
    from   Task childTasks
    join   tasks
    on     childTasks.ParentTaskId = tasks.TaskId
)

select TaskId, ParentTaskId, [Name] from tasks
   order by TaskIdList

Note that this assumes that TaskId is a string-based ID. If not, you should cast it to a varchar before concatenating it.