CTE query doesn't print the right structure?

I have this table which represents hierarchy :

childID  parentID          NAME          siblingOrder
1          1               a               0
212        1               ab              1
112        1               ac              2
412        1               ad              3
-912       112             aca             0

The structure is:

a
+----ab
+----ac
     +---aca
+----ad

(The siblingOrdercolumn is for controlling the order of ab,ac,ad )

I already have this solution which uses the siblingOrder :

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)  AS VARCHAR(1024))  AS PATH
   FROM   @myTable
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)AS VARCHAR(1024) )
    FROM   @myTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

So order by PATH actually do the job :

The problem:

The problem is that I Must(!) put values in the siblingOrder in order for it to work !

Otherwise , For example :

If I put 0 in all siblingOrder this is the result :

(yes, now sorting by path - doesn't work...)

I need that aca will always be under ac

(The only reason I added the siblingOrder is to order siblings !) and I don't want to enforce adding siblingOrder when not needed

Question :

Is it possible to enhance the query so that siblingOrder will affect only to siblings ?

I mean , If I don't care about the order of the siblings ( by putting 0) , I still expect the aca to be under ac

Sqlonline : with siblingOrder

Sqlonline - without siblingOrder

You need to include the parentID and the childIDin the path. So instead of

RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)

you should write

RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6) + RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10)

(That is in two places in your code)

Used this way you can provide the siblingOrder where desired. If present it will override the child order. If not present the children will be ordered by their childID.

SQL Fiddle without siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 0),
    (112, 1, 'ac', 0),
    (412, 1, 'ad', 0),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable 
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

|                                                                             PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER |    COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
|                                                       00000000010000000000000001 |     0 |       1 |        1 |            0 |           a |
|                            00000000010000000000000001.00000000010000000000000112 |     1 |     112 |        1 |            0 |      ----ac |
| 00000000010000000000000001.00000000010000000000000112.0000000112000000000000-912 |     2 |    -912 |      112 |            0 | --------aca |
|                            00000000010000000000000001.00000000010000000000000212 |     1 |     212 |        1 |            0 |      ----ab |
|                            00000000010000000000000001.00000000010000000000000412 |     1 |     412 |        1 |            0 |      ----ad |

SQL Fiddle with siblingOrder

MS SQL Server 2008 Schema Setup:

CREATE TABLE dbo.MyTable
    ([childID] int, [parentID] int, [NAME] varchar(3), [siblingOrder] int)
;

INSERT INTO dbo.MyTable
    ([childID], [parentID], [NAME], [siblingOrder])
VALUES
    (1, 1, 'a', 0),
    (212, 1, 'ab', 1),
    (112, 1, 'ac', 3),
    (412, 1, 'ad', 2),
    (-912, 112, 'aca', 0)
;

Query 1:

;WITH CTE AS(
   SELECT childID, parentID, 0 AS depth, NAME , siblingOrder,
         CAST(RIGHT('0000000000' + CAST(parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(childID AS VARCHAR(10)), 10)  AS VARCHAR(1024))  AS PATH
   FROM   dbo.MyTable 
   WHERE   childID = parentID 
    UNION ALL
    SELECT  TBL.childID, TBL.parentID,
           CTE.depth + 1 , TBL.name ,TBL.siblingOrder,
           CAST(cte.Path + '.' + RIGHT('0000000000' + CAST(Tbl.parentID AS VARCHAR(10)), 10) + RIGHT('00000' + CAST(Tbl.siblingOrder AS VARCHAR(6)), 6)+ RIGHT('0000000000' + CAST(Tbl.childID AS VARCHAR(10)), 10) AS VARCHAR(1024) )
    FROM   dbo.MyTable AS TBL
            INNER JOIN CTE  ON  TBL.parentID = CTE.childID
    WHERE   TBL.childID<>TBL.parentID
)
SELECT path,depth, childID, parentID, siblingOrder, REPLICATE('----', depth) + name
FROM CTE
  ORDER BY PATH

Results:

```
| PATH | DEPTH | CHILDID | PARENTID | SIBLINGORDER | COLUMN_5 |
----------------------------------------------------------------------------------------------------------------------------------------------
| 00000000010000000000000001 | 0 | 1 | 1 | 0 | a |
| 00000000010000000000000001.00000000010000010000000212 | 1 | 212 | 1 | 1 | ----ab |
| 00000000010000000000000001.00000000010000020000000412 | 1 | 412 | 1 | 2 | ----ad |
| 00000000010000000000000001.00000000

StackExchange Database Administrators Q#47585, answer score: 6