SQL Server Hierarchy Rank Display-CodePudding

I would like to seek help regarding SQL Server query.

I have a table named Students, and columns are "Id, Name, LeaderId"

Id  Name    LeaderId
1   Baldo       0
2   Cardo       1
9   Luningning  1
6   Pedro       1
3   Paolo       2
5   Reginaldo   4
4   Bernardo    5
7   Roberto     6
8   Narciso     6
10  Jopay       9
11  Policarpio  10
12  Sisa        10

Example is, Baldo has no Leader, because he is on the very top on Hierarchy structure. Leader of Cardo, Luningning, Pedro is Baldo. Leader of Paolo is Cardo. Because the LeaderId of Paolo is 2.

Please see my sample screenshot below.

My problem is how to display the rankings of hierarchy structure based on the picture above.

Having hard times to do it, try grouping by leaderId, use CTE with self join etc. but I can't get the expected output.

Expected output on query

Name Rank
Baldo 0
Cardo 1
Luningning 1
Pedro 1

Paolo 2
Roberto 2
Narcisco 2
Jopay 2

and so on....

Thank you in advance.

CodePudding user response：

Use a recursive CTE. Start with the top of the hierarchy, i.e. LeaderId = 0. Then join back to the CTE to get the subordinates of that leader, and so on.

WITH tree AS (
   SELECT Id, Name, LeaderId, CAST(0 AS INT) AS RankNum
   FROM   YourTable
   WHERE  LeaderId = 0
   UNION ALL
   SELECT c.Id, c.Name, c.LeaderId, RankNum 1
   FROM   YourTable c INNER JOIN tree 
            ON c.LeaderId = tree.Id
)
SELECT * 
FROM   tree
ORDER BY RankNum
GO

Results:

Id | Name       | LeaderId | RankNum
-: | :--------- | -------: | ------:
 1 | Baldo      |        0 |       0
 2 | Cardo      |        1 |       1
 9 | Luningning |        1 |       1
 6 | Pedro      |        1 |       1
 7 | Roberto    |        6 |       2
 8 | Narciso    |        6 |       2
10 | Jopay      |        9 |       2
 3 | Paolo      |        2 |       2
11 | Policarpio |       10 |       3
12 | Sisa       |       10 |       3

db<>fiddle here