SQL Server Hierarchy Rank Display

Question

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.

Answer 1

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

Answer 2

Showing another way to do it with hierarhcyid. I'm stealing the recursive CTE from the answer that SOS provided merely as a convenience. I'll explain in a bit why that's not necessary and how your application can maintain the hierarchyid value directly.

with YourTable as (
    select * from (values 
        (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)
    ) as x(Id, Name, LeaderId)
), tree AS (
    SELECT Id, Name, LeaderId, 
        h = cast(concat('/', Id, '/') as varchar(max)) 
    FROM   YourTable
    WHERE  LeaderId = 0

    UNION ALL

    SELECT c.Id, c.Name, c.LeaderId,
        h = concat(tree.h, c.Id, '/')
    FROM   YourTable c 
    INNER JOIN tree 
        ON c.LeaderId = tree.Id
), hid as (
    select Id, Name, LeaderId,
    h = cast(h as hierarchyid)
    from tree
)
SELECT *, 
    h.ToString(), 
    RankNum = h.GetLevel() - 1
FROM   hid
ORDER BY RankNum, h

By way of exposition for this answer, I'm calculating a value that can be cast as a hierarchyid. From there, I'm showing that you can get the desired RankNum information from the hierarchyid.

Now, why would you want to do this? In my example, I'm having to calculate the hierarchyid value at run time. But it can also be be maintained by your application as changes are made to the data! Let's look at the three types of data changes - inserts, updates, and deletes.

First, let's set up the table.

/*drop table if exists dbo.Employees;*/
create table dbo.Employees (
    EmployeeId int identity
        constraint PK_Employees primary key clustered,
    Name varchar(100) not null,
    LeaderId int null,
    ReportingStructure hierarchyid null
);
create index IX_Employees__ReportingStructure
    on dbo.Employees (ReportingStructure);
go

The only thing that's noteworthy here is that the hierarchyid data type is indexable. We'll use that fact later when updating and deleting employees. Next, let's handle creating new employee records:

create or alter procedure dbo.InsertEmployee (
    @Name varchar(100),
    @LeaderID int
)
as
begin
    set nocount on;
    declare @Id int, @ReportingStructure hierarchyid;
    declare @IdCatcher table (
        Id int
    );

    set @ReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @LeaderID
    );
    if (@ReportingStructure is null)
        set @ReportingStructure = '/';

    begin tran

        insert into dbo.Employees
            (Name, LeaderID)
        output inserted.EmployeeId
        into @IdCatcher
        values
            (@Name, @LeaderID);

        set @Id = (
            select Id
            from @IdCatcher
        );

        update dbo.Employees
        set ReportingStructure = concat(@ReportingStructure.ToString(), @Id, '/')
        where EmployeeId = @Id;

    commit
end
go

This is fairly straightforward. The steps are:

• Find the ReportingStructure for the passed in Leader.
  • If none is found, they'll be starting their own tree and use their EmployeeID to seed it
• Insert the new record and get the EmployeeID from it
• Update the new record with the correct ReportingStructure.

Next we'll handle updates. Note, the only updates we're interested in for this exercise are updates to someone's manager. That is, changing someone's name doesn't affect their place in the org chart and so we can ignore it here.

create or alter procedure dbo.UpdateEmployeeLeader (
    @EmployeeID int,
    @NewLeaderID int
)
as
begin
    set nocount on;
    declare @newManagerReportingStructure hierarchyid,
        @oldReportingStructure hierarchyid;

    set @newManagerReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @NewLeaderID
    );
    set @oldReportingStructure = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @EmployeeID
    );

    if (@oldReportingStructure is not null and @newManagerReportingStructure is not null)
    begin
        begin tran
            update dbo.Employees
            set LeaderId = @NewLeaderID
            where EmployeeId = @EmployeeID;

            with cte as (
                select ReportingStructure,
                    NewReportingStructure = 
                    ReportingStructure.GetReparentedValue(
                        @oldReportingStructure.GetAncestor(1),
                        @newManagerReportingStructure
                    )
                from dbo.Employees
                where ReportingStructure.IsDescendantOf(@oldReportingStructure) = 1
            )
            update cte
            set ReportingStructure = NewReportingStructure
        commit tran
    end
end
go

The plain language explanation of what's happening here is:

• Find the ReportingStructure for the new manager and the employee being moved
• Update the requested employee as well as all employees that report to them (directly or indirectly) to reflect the new reporting structure.

That last bit I think warrants some explanation. The "all employees that report to them (directly or indirectly)" is accomplished by the ReportingStructure.IsDescendantOf(@oldReportingStructure) = 1 bit of code. As a note, it also identifies the Employee themselves (as IsDescendantOf() considers values to be descendants of themselves). Also noteworthy is that the call to IsDescendantOf() is efficient because of that index that we created on the ReportingStructure column.

The "to reflect the new reporting structure" bit is accomplished via the GetReparentedValue() bit of code. I like to think of what's happening there as a string replacement of some leading part of the path with a new leading part. Extending the tree metaphor, it's like you're taking a branch from one tree and grafting it onto another tree.

Lastly, let's deal with deletes.

create or alter procedure dbo.DeleteEmployee (
    @EmployeeID int
)
as
begin
    set nocount on;
    declare @ReportingStructure hierarchyid = (
        select ReportingStructure
        from dbo.Employees
        where EmployeeId = @EmployeeID
    );
    if not exists (
        select 1
        from dbo.Employees
        where ReportingStructure.IsDescendantOf(@ReportingStructure) = 1
            and EmployeeId <> @EmployeeID
    )
        delete dbo.Employees
        where EmployeeId = @EmployeeID;
    else
    begin
        raiserror(
            'Employee still has direct reports. Update their leader before deleting.',
            16,
            1
        );
    end
end
go

This is pretty straightforward. We're just checking to see that this employee has any reports (direct or otherwise, though the previous update procedure should ensure that we don't have any data that "skips" a level) before deleting.

To wrap it all up, here are calls to these procs that deal with operations within your data. Let's hire some employees!

exec dbo.InsertEmployee @name = 'Baldo', @LeaderID = 0;
exec dbo.InsertEmployee @name = 'Cardo', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Luningning', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Pedro', @LeaderID = 1;
exec dbo.InsertEmployee @name = 'Paolo', @LeaderID = 2;
exec dbo.InsertEmployee @name = 'Reginaldo', @LeaderID = 4;
exec dbo.InsertEmployee @name = 'Bernardo', @LeaderID = 5;
exec dbo.InsertEmployee @name = 'Roberto', @LeaderID = 6;
exec dbo.InsertEmployee @name = 'Narciso', @LeaderID = 6;
exec dbo.InsertEmployee @name = 'Jopay', @LeaderID = 9;
exec dbo.InsertEmployee @name = 'Policarpio', @LeaderID = 10;
exec dbo.InsertEmployee @name = 'Sisa', @LeaderID = 10;

Bad news... Reginaldo just quit. Let's update Narciso's manager to be Paolo

exec dbo.UpdateEmployeeLeader @EmployeeID = 9, @NewLeaderID = 5;
exec dbo.DeleteEmployee @EmployeeID = 6;

Oops... we missed Roberto! Let's have him also report to Paolo for now.

exec dbo.UpdateEmployeeLeader @EmployeeID = 8, @NewLeaderID = 5;
exec dbo.DeleteEmployee @EmployeeID = 6;

The last thing I'll say about this approach is that I still like having LeaderID be a column in the table. Why? I would consider that to be the source of truth for "who reports to whom?" and the ReportingStructure column is merely a materialized path from leaf to root in the tree. If something happens where ReportingStructure gets corrupted (i.e. doesn't accurately reflect the reporting structure), we can recalculate it from the LeaderID/EmployeeID links.