SqlServer Hierarchical parent/child query with sort of children inside 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 get 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 tree in this format. Here please note the children within each parent are sorted as well. Indentation is giving so as to give a better idea of the result -

Electronics
    TV
        LED
            LG
            Sony
        Plasma
KitchenAppliances
    Stoves
        Electric
        Gas

This is the query I wrote, but does not seem to be working. The sort number logic seems to be broken. Could 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 are doing it almost right. The only things I changed are: condition WHERE sc1.ID is null in non-recursive part of the cte changed to WHERE sc1.ParentID is null, and the way sort key (path) is calculated:

;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, there are two paths calculated, first one is for sorting of elements and second one is for viewing. In case if path of the tree element is not supposed to be shown anywhere, you may leave only SortPath.