SQL Server CTE for recursion and sequencing

Question

I have the following table in SQL Server (2012):

Mytable

Id      __ParentId      Priority
1       NULL            NULL    
2       1               100     
3       1               300     
4       1               200     
5       4               100     
6       4               200     
7       6               100     
8       5               100     
9       5               200     
10      9               100     
11      5               50      

The __ ParentId column refers to Id to know the parent of any row, and it can go to many levels of recursion (for example, Id is a 8child 5, which is a child 4, which is a child 1).

In addition, there is a Priority column showing the order in which children should appear in the parent element (with the lowest priority).

So, the final table I would like to get is:

Id      __ParentId  Priority    Order   
1       NULL        NULL        1       
2       1           100         2       
4       1           200         3       
5       4           100         4       
11      5           50          5       
8       5           100         6       
9       5           200         7       
10      9           100         8       
6       4           200         9       
7       6           100         10      
3       1           300         11      

, , 2 1 , , , 4 , , .

, :

 1
    2
    4
        5
            11
            8
            9
                10
        6
            7
    3

CTE, , , SQL, .

Answer 1

SQL2008 +:

:

DECLARE @TableA TABLE (
    Id          INT NOT NULL PRIMARY KEY,
    __ParentId  INT NULL,
    [Priority]  INT NULL
);

INSERT @TableA (Id, __ParentId, [Priority])
VALUES 
(1 ,NULL,NULL),   
(2 ,1   ,100 ),   
(3 ,1   ,300 ),   
(4 ,1   ,200 ),   
(5 ,4   ,100 ),   
(6 ,4   ,200 ),   
(7 ,6   ,100 ),   
(8 ,5   ,100 ),   
(9 ,5   ,200 ),   
(10,9   ,100 ),   
(11,5   ,50  );

WITH CteRecursive
AS (
    SELECT  a.Id, a.__ParentId, a.[Priority], CONVERT(HIERARCHYID, '/' + LTRIM(a.Id) + '/') AS HID
    FROM    @TableA a
    WHERE   a.__ParentId IS NULL
    UNION ALL 
    SELECT  cld.Id, cld.__ParentId, cld.[Priority], CONVERT(HIERARCHYID, prt.HID.ToString() + LTRIM(cld.[Priority]) + '/') AS HID
    FROM     CteRecursive prt -- Parent
    JOIN @TableA cld ON prt.Id = cld.__ParentId -- Child
    WHERE   cld.__ParentId IS NOT NULL
)
SELECT *, r.HID.ToString() AS HIDToString FROM CteRecursive r
ORDER BY r.HID ASC

:

# 1: HIERARCHYID ordering: HID ( , ).

a b, b , a b . - , , . , . . (SQL Server).

Reference

Answer 2

cte, . SEQUENCE, row_numbers ( ) Priority.

Declare @YourTable Table ([Id] varchar(50),[__ParentId] varchar(50),[Priority] varchar(50))
Insert Into @YourTable Values
 (1,NULL,NULL)
,(2,1,100)
,(3,1,300)
,(4,1,200)
,(5,4,100)
,(6,4,200)
,(7,6,100)
,(8,5,100)
,(9,5,200)
,(10,9,100)
,(11,5,50)

Declare @Top    int         = null      --<<  Sets top of Hier Try 4
Declare @Nest   varchar(25) = '|-----'  --<<  Optional: Added for readability

;with cteP as (
      Select Seq  = cast(10000+Row_Number() over (Order by [Priority]) as varchar(500))
            ,ID
            ,__ParentId 
            ,Lvl=1
            ,Priority
      From   @YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(__ParentId ,-1) else ID end
      Union  All
      Select Seq  = cast(concat(p.Seq,'.',10000+Row_Number() over (Order by r.[Priority])) as varchar(500))
            ,r.ID
            ,r.__ParentId 
            ,p.Lvl+1
            ,r.Priority
      From   @YourTable r
      Join   cteP p on r.__ParentId  = p.ID)
Select A.ID
      ,A.__ParentId 
      ,A.Lvl
      ,A.Priority
      ,Name = Replicate(@Nest,A.Lvl-1) +cast(ID as varchar(25))
 From cteP A
 Order By Seq