SQL query for tree table menu

Question

I have a table with tree structure:

ID    Title           ParentID     Orderby 
----------------------------------------
1     All             0            2
2     Banking         1            5
3     USAA Checking   0            0
4     USAA Mastercard 1            9
5     Medical         3            0
6     Jobs            3            100
7     Archive         0            1
8     Active          7            0  
9     BoA Amex        1            1

I need to write a SQL query to produce a result like this (ORDER by column Orderby):

ID    Title           Path         Orderby 
----------------------------------------
3     USAA Checking   1            0
5     Medical         1.1          0
6     Jobs            3.2          100
7     Archive         2            1
8     Active          2.1          0
1     All             3            2
9     BoA Amex        3.1          1
2     Banking         3.2          5
4     USAA Mastercard 3.3          9

Who can help me to write a SQL query? Thanks!

Answer 1

We can do this using a recursive CTE:

WITH children AS (
    SELECT NULL AS ParentID, ID, Title, Orderby,
        CAST(ID AS VARCHAR(500)) AS Path
    FROM Categories
    WHERE ParentID = 0

    UNION ALL

    SELECT 
       d.ParentID, t.counter + 1, d.ID, d.Title, d.Orderby,
       CAST(CAST(t.Path AS VARCHAR(50)) + '.' +
       CAST(ROW_NUMBER() OVER (PARTITION BY d.ParentID ORDER BY d.ID) AS VARCHAR(50)) AS VARCHAR(500))
    FROM children t
    INNER JOIN Categories AS d
        ON d.ParentID = t.ID
)

SELECT ID, Title, Path, Orderby
FROM children;

Demo

Note that you never provided fixed logic for what should be used to determine the minor version numbers, for a given parent version. That is, it is not clear why Medical appears earlier than Jobs in the hierarchy.