SqlServer Hierarchical parent / child query with a view of the child elements inside the parent

Question

I have a table with data like this -

ID ParentID ProductTypeName 1 NULL Electronics 200 1 TV 300 200 Plasma 67 NULL KitchenAppliances 78 67 Stoves 82 78 Electric 99 78 Gas 23 200 LED 65 300 LG 66 300 Sony 

I would like to receive the data in the following format -

 ID ParentID ProductTypeName Level Sort(Or Some kind of sort value) 1 NULL Electronics 0 1 200 1 TV 1 110 300 200 LED 2 120 65 300 LG 3 12010 66 300 Sony 3 12020 23 200 Plasma 2 100030 67 NULL KitchenAppliances 0 10000010 78 67 Stoves 1 1000001010 82 78 Electric 2 100000101020 99 78 Gas 2 100000101030 

To display data in a tree in this format. Please note here that the children in each parent are also sorted. Indents give to better understand the result -

 Electronics TV LED LG Sony Plasma KitchenAppliances Stoves Electric Gas 

This is a query that I wrote, but does not seem to work. The logic of the sort number seems to be broken. Can someone help with this. Any help appreciated. Thanks.

 ;WITH cte (ID, ParentID, [Level], [Name], Sort) AS( SELECT sc1.ID, NULL, 0, sc1.Name, cast(row_number()over(partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max)) as Sort FROM TableData sc1 WHERE sc1.ID is null UNION ALL SELECT sc2.ID, sc2.ParentID, g2.[level] + 1, sc2.Name, g2.Sort + cast(row_number()over(partition by sc2.ParentCategoryID order by sc2.Name) as varchar(max))Sort FROM dbo.TableData sc2 INNER JOIN cte g2 ON sc2.ParentID = g2.ID 

Answer 1

You do it almost right. The only thing I changed: the WHERE sc1.ID is null condition in the non-recursive part of cte, changed to WHERE sc1.ParentID is null , and a way to sort the key (path):

 ;WITH cte (ID, ParentID, [Name], Level, SortPath, DisplayPath) AS( SELECT sc1.ID, NULL, sc1.Name, 0, cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varbinary(max)), cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(max)) FROM dbo.TableData sc1 WHERE sc1.ParentID is null UNION ALL SELECT sc2.ID, sc2.ParentID, sc2.Name, g2.Level + 1 g2.SortPath + cast(row_number() over (partition by sc2.ParentCategoryID order by sc2.Name) as binary(4)), g2.DisplayPath + '.' + cast(row_number() over (partition by sc1.ParentCategoryID order by sc1.Name) as varchar(10)) FROM dbo.TableData sc2 JOIN cte g2 ON sc2.ParentID = g2.ID ) select ID, ParentID, Name, DisplayPath from cte order by SortPath 

As you can see, two paths are calculated: first for sorting the elements, and the second for viewing. If the path to the tree element should not be displayed anywhere, you can leave only SortPath.