Organizing data into hierarchical structure with SQL Server

I have a SQL query that I used with SSRS for creating a hierarchical employee report. SSRS had the ability to have recursive parent groupings, which explains the following table structure of the data.

This is how I organized my hierarchical employee list table:

This is how I create my employee table:

BEGIN

DECLARE @ReportingDate date = GETDATE()
SET @ReportingDate = DATEADD(day, -1, @ReportingDate)

DECLARE @EmployeeTopMangerList TABLE 
        (
            RowID int IDENTITY (1, 1),
            [emplid] varchar(255), 
            EmpNTID varchar(255),
            [name] varchar(255), 
            TopManagerEmplID varchar(255),
            TopManagerEmpNTID varchar(255),
            TopManagerName varchar(255)
        )

---*****************add in loop
DECLARE @Employees TABLE
        (
            rowid int IDENTITY (1, 1),
            EmpNTID varchar(255)
        )

INSERT INTO @Employees
    SELECT DISTINCT NTID 
    FROM [dbo].DAILY_ACTIVE_RFCS_RPT
    WHERE ACTL_PCKG_RLS_DATE >= DATEADD(month, -8, DATEADD(DAY, 1, EOMONTH(@ReportingDate, 0)))

-- Get the number of rows in the looping table
DECLARE @RowCount INT
SET @RowCount = (SELECT COUNT(ROWID) FROM @Employees) 

-- Declare an iterator
DECLARE @I INT

-- Initialize the iterator
SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
BEGIN
    -- Declare variables to hold the data which we get after looping each record 
    DECLARE @iEmpNTID varchar(max), @iEmplID varchar(max)
    DECLARE @TM_Name varchar(200), @TM_EmplID varchar(25), @TM_EmpNTID varchar(25)

    -- Get the data from table and set to variables
    SELECT @iEmpNTID = EmpNTID   
    FROM @Employees 
    WHERE ROWID = @I;
 
    WITH Hierarchy([emplid],EmpNTID, [name], RMID,  HLevel) AS
    (
        SELECT 
            [emplid], 
            EmpNTID,
            [name], 
            RMID,
            0 AS HLevel
        FROM 
            vw_Employees_Combined 
        WHERE 
            -- EmplID = @iEmplID
            EmpNTID = @iEmpNTID

        UNION ALL

        SELECT 
            SubDepartment.[emplid], 
            SubDepartment.EmpNTID,
            SubDepartment.[name], 
            SubDepartment.RMID,
            HLevel + 1
        FROM 
            vw_Employees_Combined SubDepartment
        INNER JOIN 
            Hierarchy ParentDepartment ON SubDepartment.[emplid] = ParentDepartment.RMID 
    )
    SELECT TOP 1
        @TM_EmplID = [emplid], 
        @TM_EmpNTID = EmpNTID,
        @TM_Name = [name]
    FROM  
        Hierarchy
    ORDER BY
        HLevel DESC

INSERT INTO @EmployeeTopMangerList ([emplid], EmpNTID,[name], TopManagerEmplID, TopManagerEmpNTID, TopManagerName)
    SELECT 
        [emplid], EmpNTID, [name], @TM_EmplID, @TM_EmpNTID, @TM_Name 
    FROM 
        vw_Employees_Combined 
    WHERE 
        EmpNTID = @iEmpNTID     -- EmplID = @iEmplID

    -- -- Increment the iterator
    SET @I = @I + 1
END
----************************    

INSERT INTO @EmployeeTopMangerList ([emplid],EmpNTID,[name],TopManagerEmplID, TopManagerEmpNTID,TopManagerName )
    SELECT DISTCINT 
        a.NTID, a.NTID, a.assgn, '000000', 'Unknown', ' Unknown Manager' 
    FROM
        [dbo].DAILY_ACTIVE_RFCS_RPT a
    WHERE
        a.NTID NOT IN (SELECT EmpNTID FROM @EmployeeTopMangerList)

DECLARE @TopManagersTable TABLE
        (
            rowid int IDENTITY(1,1),
            TopManagerEmplID varchar(255),
            TopManagerEmpNTID varchar(255),
            TopManagerName varchar(255)
        )

INSERT INTO @TopManagersTable (TopManagerEmplID, TopManagerEmpNTID, TopManagerName)
    SELECT DISTINCT 
        TopManagerEmplID, TopManagerEmpNTID, TopManagerName 
    FROM
        @EmployeeTopMangerList

declare @EmployeeList table(
RowID int identity (1,1),
 EmplID varchar(255), 
 EmpNTID varchar(255), 
 [name] varchar(255),
 RMID varchar(255),
 TopManagerEmplID varchar(255)
 );

--------------------------------------------------------------------------
 SET @RowCount = (SELECT COUNT(ROWID) FROM @TopManagersTable) 

-- Declare an iterator
-- Initialize the iterator
 SET @I = 1

-- Loop through the rows of a table @myTable
WHILE (@I <= @RowCount)
 BEGIN
 -- Declare variables to hold the data which we get after looping each record 
-- Get the data from table and set to variables
 SELECT @iEmpNTID = TopManagerEmpNTID   FROM @TopManagersTable WHERE ROWID = @I;


with src as (
  select 
    [emplid]
    ,EmpNTID
    , [name]
    , case 
        when [emplid] = RMID then null --@CurrentUserEmplID then null--[supervisor_id] then null 
        else RMID 
     end as RMID
  from vw_Employees_Combined
), cte as ( 
  select [emplid],EmpNTID, [name], RMID, [emplid] as top_manager
  from src
  where EmpNTID = @iEmpNTID --1 -- this is the manager you're interested in
                     -- it can be a sub-manager in the middle or the tree
union all            -- or the top level manager rooting the tree

  select src.[emplid],src.EmpNTID, src.[name], src.RMID, top_manager
  from src 
  inner join cte on src.RMID = cte.[emplid]
  where src.[emplid] <> top_manager
)
insert into @EmployeeList
select * from cte;

-- -- Increment the iterator
SET @I = @I + 1

END
----************************    
------------------------------------------------------------------------------
--select count(*) from @EmployeeList
--select distinct emplid from @EmployeeList

Declare @EmployeeListLevelList as table(
 EmplID varchar(255), 
 EmpNTID varchar(255),
 [name] varchar(255),
 RMID varchar(255),
 RMNTID varchar(255),
 TopManagerEmplID varchar(255),
 TopManagerEmpNTID varchar(255),
 [Level] int
 );

 Insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'1'
 from @EmployeeList where RMID is null

 insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'2'
 from @EmployeeList where RMID = TopManagerEmplID

  insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'3'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '2')

   insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'4'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '3')

   insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'5'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '4')

    insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'6'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '5')

    insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'7'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '6')

    insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'8'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '7')

    insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'9'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '8')

     insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'10'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '9')

    insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'11'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '10')

     insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'12'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '11')

     insert into @EmployeeListLevelList(EmplID,EmpNTID,[name],RMID,TopManagerEmplID,[Level])
 select EmplID,EmpNTID,[name],RMID,TopManagerEmplID,'13'
 from @EmployeeList where  RMID in (select EmplID from @EmployeeListLevelList where [Level] = '12')

  update a
 set RMNTID = b.EMPNTID
 from @EmployeeListLevelList a, @EmployeeListLevelList b
 where a.RMID = b.EMPLID

  update a
 set TopManagerEmpNTID = b.EMPNTID
 from @EmployeeListLevelList a, @EmployeeListLevelList b
 where a.TopManagerEmplID = b.EMPLID

 truncate table DAILY_EmployeeListLevelList_BADGED
 insert into DAILY_EmployeeListLevelList_BADGED ([EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level])
 select Distinct [EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level] from @EmployeeListLevelList
 where EmplID is not null

truncate table DAILY_EmployeeListLevelList_Combined

insert into DAILY_EmployeeListLevelList_Combined (
[EmplID], [EmpNTID], [name], [MMID], [MMNTID], [MMTopManagerEmplID], [MMTopManagerEmpNTID], [MMLevel],
[RMID], [RMNTID], [RMTopManagerEmplID], [RMTopManagerEmpNTID], [RMLevel]
)

SELECT [EmplID], [EmpNTID], [name], [RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level],
[RMID], [RMNTID], [TopManagerEmplID], [TopManagerEmpNTID], [Level]
FROM DAILY_EmployeeListLevelList_BADGED 

END

We've moved on from SSRS to using Angular/Highcharts for reporting. I am having a difficult time modifying my original query to return something that allows me to create a report in Angular that allows a user to drill down on an employee and see the people who report to them:

+ Carmen            
    + Annette       
        + Eric  
            Alan
            Courtney
            Robert
            Michael
    + Shireen       
        Alton   
        John    
    + Jeffrey       
        Lynn    
        Abhishek    

How can I structure my employee data to provide a report in Angular/Highcharts that allows me to expand a user to see the employees below them?

Consider recursive query.
We select the main employees in the anchor part, considering that they have null in the link to the upper level. You can also choose according to another criterion.
In the recursive part, you choose the next level of accountability. There are no unnecessary steps here. You don't need to filter anything.

It would be interesting to see the result on a query to a table of 100K rows. And compare with your current method.
I added "path" column for clarity.
Recursion level is equal to hierarchy deep.

See example

with r as(
  select 0 as lvlN
    ,EmpNTID as TopManagerId,EmpNTID,name,RMNTID 
    ,cast(concat(name,':') as varchar(1000)) path
  from Hierarchy
  where TopManagerEmpNTID is null
  union all
  select lvlN+1 as lvlN
    ,TopManagerId,t.EmpNTID,t.name,t.RMNTID 
    ,cast(concat(path,'->',t.name) as varchar(1000)) path
  from r inner join Hierarchy t  on t.RMNTID=r.EmpNTID and r.EmpNTID<>t.EmpNTID
)
select * from r order by lvlN,EmpNTID;

fiddle

With

select * from r order by path;

Output is more like your hierarchical shcema.

fiddle

You can choose the appropriate output. For example, this.

with r as(
  select 0 as lvlN
    ,EmpNTID as TopManagerId,EmpNTID,name,RMNTID -- ,TopManagerEmpNTID,Level
    ,cast(concat('0.',EmpNTID) as varchar(1000)) Id
    ,cast(concat('0','.0') as varchar(1000)) parent
  from Hierarchy
  where TopManagerEmpNTID is null
  union all
  select lvlN+1 as lvlN
    ,TopManagerId,t.EmpNTID,t.name,t.RMNTID -- ,t.TopManagerEmpNTID,t.Level
    ,cast(concat(lvlN+1,'.',t.EmpNTID) as varchar(1000)) Id
    ,cast(r.Id as varchar(1000)) parent
  from r inner join Hierarchy t  on t.RMNTID=r.EmpNTID and r.EmpNTID<>t.EmpNTID
  where lvlN<11
)
select * from r order by Id;

Or

[  
  {Id:'0.1000',parent:'0.0',name:'Carmen'}, 
  {Id:'1.1001',parent:'0.1000',name:'Annette'}, 
  {Id:'1.1002',parent:'0.1000',name:'Shireen'},   
  {Id:'1.1003',parent:'0.1000',name:'Jeffrey'}, 
  {Id:'2.1007',parent:'1.1003',name:'Lynn'}, 
  {Id:'2.1008',parent:'1.1003',name:'Abhishek'}, 
  {Id:'2.1005',parent:'1.1002',name:'Alton'}, 
  {Id:'2.1006',parent:'1.1002',name:'John'}, 
  {Id:'2.1004',parent:'1.1001',name:'Eric'}, 
  {Id:'3.1009',parent:'2.1004',name:'Alan'}, 
  {Id:'3.1010',parent:'2.1004',name:'Courtney'}, 
  {Id:'3.1011',parent:'2.1004',name:'Robert'}, 
  {Id:'3.1012',parent:'2.1004',name:'Michael'}
]