T-SQL Ordering a Recursive Query - Parent/Child Structure

Question

I am trying (and failing) to correctly order my recursive CTE. My table consists of a parent-child structure where one task can relate to another on a variety of different levels.

For example I could create a task (this is the parent), then create a sub-task from this and then a sub-task from that sub-task and so forth..

Below is some test data that I have included. Currently it's ordered by Path which orders it alphabetically.

So if I were to create a task. It would give me a TaskID for that task (Say 50) - I could then create 5 subtasks for that main task (51,52,53,54,55). I could then add sub-tasks to the 5 subtasks (51->56) (53->57) but when I want the order I would need it returning

So the order I would require it

• 50
  • 51
    • 56
  • 52
  • 53
    • 57
  • 54
  • 55

Proper order of the test data

Here is the code that I have been using

DECLARE @TaskID NUMERIC(10,0)

SET @TaskID = 38

;WITH cte AS 
(
SELECT 
    t.TaskID
    ,t.ParentID
    ,t.Title
    ,CONVERT(VARCHAR(MAX),'') AS [Nest]
    ,CONVERT(VARCHAR(MAX),'') AS [Path]
    ,t.CreatedDate
FROM 
    tasks.Tasks t
WHERE 
    t.ParentID IS NULL
    AND t.TaskID = @TaskID

UNION ALL

SELECT 
    sub.TaskID
    ,sub.ParentID
    ,sub.Title
    ,cte.[Nest] + CONVERT(VARCHAR(MAX),sub.TaskID) AS [Nest]
    ,cte.[Path] + ',' + CONVERT(VARCHAR(MAX),sub.TaskID) AS [Path]
    ,sub.CreatedDate
FROM 
    tasks.Tasks sub
    INNER JOIN cte ON cte.TaskID = sub.ParentID
)

SELECT 
    TaskID
    ,ParentID
    ,Title
    ,Nest
    ,[Path]
    ,CreatedDate
FROM (
SELECT 
    cte.TaskID
    ,cte.ParentID
    ,cte.Title
    ,NULLIF(LEN(cte.[Path]) - LEN(REPLACE(cte.[Path], ',', '')),0) Nest
    ,CONVERT(VARCHAR(25),@TaskID) + cte.[Path] AS [Path]
    ,cte.CreatedDate
FROM 
    cte
)a
ORDER BY
    a.[Path]

I have a feeling it will be blindingly obvious but I'm really not sure how to proceed. I thought about more recursion, functions, splitting the string with no success.

Apologies if I'm not being clear

Answer 1

The easiest way would be to pad the keys to a fixed length. e.g. 038,007 will be ordered before 038,012 But the padding length would have to be safe for the largest taskid. Although you could keep your path trimmed for readability and create an extra padded field for sorting.

A somewhat safer version would be to do the same, but create a padded path from row_numbers. Where the padding size would have to be big enough to support the maximum number of sub items.

DECLARE @TaskID NUMERIC(10,0)

SET @TaskID = 38

declare @maxsubchars int = 3 --not more than 999 sub items

;with cte as
(
SELECT 
    t.TaskID
    ,t.ParentID
    ,t.Title
    ,0 AS [Nest]
    ,CONVERT(VARCHAR(MAX),t.taskid) AS [Path]
    ,CONVERT(VARCHAR(MAX),'') OrderPath 
    ,t.CreatedDate  
FROM 
    tasks.Tasks t
WHERE 
    t.ParentID IS NULL
    AND t.TaskID = @TaskID

union all

SELECT 
    sub.TaskID
    ,sub.ParentID
    ,sub.Title
    ,cte.Nest + 1
    ,cte.[Path] + ',' + CONVERT(VARCHAR(MAX),sub.TaskID) 
    ,cte.OrderPath + ',' + right(REPLICATE('0', @maxsubchars) + CONVERT(VARCHAR,ROW_NUMBER() over (order by  sub.TaskID)), @maxsubchars) 
    ,sub.CreatedDate
FROM 
    tasks.Tasks sub
    INNER JOIN cte ON cte.TaskID = sub.ParentID
)
select taskid, parentid, title,nullif(nest,0) Nest,Path, createddate from cte order by  OrderPath

You could probably go more fancy than a fixed subitem length, determining the amount of subitems and basing the padding on said length. Or using numbered rows based on the amount of siblings and traverse in reverse direction and maybe (just spouting some untested thoughts), but using a simple ordered path is likely enough.

Answer 2

It is very simple. Yuu dont need to use any loops or functions. I assume you already derived PATH value. Based on this i derived the solution.

SELECT C.TASKID,  REPLICATE(' ', (LEN([PATH]) - LEN(REPLACE([PATH],',','')) + 2) ) + CONVERT(NVARCHAR(20),C.TASKID), [PATH]
FROM CTE C
ORDER BY [PATH]

Answer 3

If the topmost CTE (as in the below query) is your table structure then the below code could be the solution.

WITH CTE AS
(
    SELECT 7112 TASKID ,NULL PARENTID UNION ALL
    SELECT 7120 TASKID ,7112 ParanetID UNION ALL
    SELECT 7139 TASKID ,7112 ParanetID UNION ALL
    SELECT 7150 TASKID ,7112 ParanetID UNION ALL
    SELECT 23682 TASKID ,7112 ParanetID UNION ALL
    SELECT 7100 TASKID ,7112 ParanetID UNION ALL
    SELECT 23691 TASKID ,7112 ParanetID UNION ALL
    SELECT 23696 TASKID ,7112 ParanetID UNION ALL
    SELECT 23700 TASKID ,23696 ParanetID UNION ALL
    SELECT 23694 TASKID ,23691 ParanetID UNION ALL
    SELECT 23689 TASKID ,7120 ParanetID UNION ALL
    SELECT 7148 TASKID ,23696 ParanetID UNION ALL
    SELECT 7126 TASKID ,7120 ParanetID UNION ALL
    SELECT 7094 TASKID ,7120 ParanetID UNION ALL
    SELECT 7098 TASKID ,7094 ParanetID UNION ALL
    SELECT 23687 TASKID ,7094 ParanetID 

)
,RECURSIVECTE AS
(
SELECT TASKID, CONVERT(NVARCHAR(MAX),convert(nvarchar(20),TASKID)) [PATH]
FROM CTE
WHERE PARENTID IS NULL 
UNION ALL
SELECT C.TASKID, CONVERT(NVARCHAR(MAX),convert(nvarchar(20),R.[PATH]) + ',' + convert(nvarchar(20),C.TASKID))
FROM RECURSIVECTE R
INNER JOIN CTE C ON R.TASKID = C.PARENTID
)
SELECT C.TASKID, REPLICATE(' ', (LEN([PATH]) - LEN(REPLACE([PATH],',','')) + 2) ) + '.' + CONVERT(NVARCHAR(20),C.TASKID)
FROM RECURSIVECTE C
ORDER BY [PATH]

Try to this query in Text output mode in SSMS. So that you could see the difference