Take a look at this example:
CREATE TABLE Employees
(
Id int IDENTITY(1,1),
EmployeeName varchar(30),
ParentId int
);
INSERT INTO Employees(EmployeeName, ParentId)
VALUES('name1', null),
('name2', null),
('name3', 1),
('name4', 2),
('name5', 3),
('name6', 3);
;WITH CTE AS
(
SELECT t.Id, t.EmployeeName, t.ParentId, 1 as [Lvl]
FROM Employees t
WHERE t.ParentId IS NULL
UNION ALL
SELECT t.Id, t.EmployeeName, t.ParentId, c.Lvl + 1
FROM Employees t
INNER JOIN CTE c on t.ParentId = c.Id
WHERE t.ParentId IS NOT NULL
)
SELECT *
FROM
(
SELECT *, ROW_NUMBER() OVER(PARTITION BY Lvl ORDER BY Id) AS RN
FROM CTE
) AS C
ORDER BY c.RN, c.Lvl;
Result:
Id EmployeeName ParentId Lvl RN
1 name1 1 1
3 name3 1 2 1
5 name5 3 3 1
2 name2 1 2
4 name4 2 2 2
6 name6 3 3 2
SQL Server 2019 | db<>fiddle[
^]