Ordering SQL query by hierarchy and it's random code

Question

I've tried to search for something related and similar, but, couldn't find it.

This is a table that I need to get as result:

+-----+-----------+------+-------------------+
| ID  | PARENT_ID | CODE |       NAME        |
+-----+-----------+------+-------------------+
| 218 | NULL      | 1445 | First One         |
| 235 | 218       |    2 | First Child       |
| 247 | 235       |   45 | First Grandchild  |
| 246 | 235       |   55 | Second Grandchild |
| 230 | 218       |    3 | Second Child      |
| 238 | 230       |   12 | Third Grandchild  |
| 231 | 230       |   20 | Fourth Grandchild |
+-----+-----------+------+-------------------+

The order must be by it's hierarchy followed by it's code. I need this to make an assertion. And, if it's possible, I would like to get this only doing a query, without a method to sort this list. This is a sample of what I'm trying to assert: Tree Hierarchy

What I've done so far, it's the following recursive query:

WITH CTE (ID, PARENT_ID, CODE, NAME)
AS
-- Anchor:
    (SELECT
        ID,
        PARENT_ID,
        CODE,
        NAME
        FROM WAREHOUSE
        WHERE PARENT_ID IS NULL

    UNION ALL 

-- Level:
    SELECT
        W.ID,
        W.PARENT_ID,
        W.CODE,
        W.NAME
        FROM WAREHOUSE AS W
        INNER JOIN CTE
        ON R.PARENT_ID = CTE.ID)

SELECT *
    FROM CTE

I appreciate any help on this! Thanks in advance!

Answer 1

Looks like you can use the [CODE] sequence in a hierarchyid path

Example

Declare @YourTable Table ([ID] int,[PARENT_ID] int,[CODE] varchar(50),[NAME] varchar(50))
Insert Into @YourTable Values 
 (218,NULL,1445,'First One')
,(235,218,2,'First Child')
,(247,235,45,'First Grandchild')
,(246,235,55,'Second Grandchild')
,(230,218,3,'Second Child')
,(238,230,12,'Third Grandchild')
,(231,230,20,'Fourth Grandchild')


;with cteP as (
      Select ID
            ,PARENT_ID 
            ,[Code]
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',[Code],'/'))
      From   @YourTable
      Where  Parent_ID is null
      Union  All
      Select ID  = r.ID
            ,PARENT_ID  = r.PARENT_ID 
            ,r.[Code]
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.[Code],'/'))
      From   @YourTable r
      Join   cteP p on r.PARENT_ID  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,PARENT_ID
      ,[Code]
      ,Name  
 From cteP A
 Order By A.HierID

Returns

Lvl ID  PARENT_ID   Code    Name
1   218 NULL        1445    First One
2   235 218         2       First Child
3   247 235         45      First Grandchild
3   246 235         55      Second Grandchild
2   230 218         3       Second Child
3   238 230         12      Third Grandchild
3   231 230         20      Fourth Grandchild