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
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