sql server - Get all employees under manager with CTE

Question

I have a table which has employee details

EmpId ManagerId Level Value
1      0         5     CEO
2      1         4     EMP
3      1         4     ORG
4      2         3     NULL
5      2         3     NULL
6      2         2     NULL
7      1         1     NULL
8      5         0     NULL

Now, I have to start wil Employee Id 2 and found all it's low level hirerachy (i.e. 2, 4, 5, 6, 8) and assign them value same as "2" (i.e. EMP).

Expected output :

  EmpId ManagerId Level Value
    1      0         5     CEO
    2      1         4     EMP
    3      1         4     ORG
    4      2         3     EMP
    5      2         3     EMP
    6      2         2     EMP
    7      1         1     NULL
    8      5         0     EMP

What I am trying:

    ; WITH LevelHire AS
(
        SELECT EmpId, ManagerId,Level
        FROM EmployeeTable
        WHERE EmpId =2
        UNION ALL
        SELECT Lh.EmpId,  RC.ManagerId, Lh.Level
        FROM LevelHire LH
        INNER JOIN [EmployeeTable] RC
        ON LH.EmpId= RC.EmpId

)

SELECT * FROM LevelHire
option (maxrecursion 0)

How can I achieve the same?

Answer 1

you can try something like this

;WITH EmployeeTable AS 
(
SELECT 1 EmpId,0 ManagerId ,   5 Level ,'CEO' Value
UNION ALL SELECT 2,1,   4,'EMP'
UNION ALL SELECT 3,1,   4,'ORG'
UNION ALL SELECT 4,2,   3,NULL
UNION ALL SELECT 5,2,   3,NULL
UNION ALL SELECT 6,2,   2,NULL
UNION ALL SELECT 7,1,   1,NULL
UNION ALL SELECT 8,5,   0,NULL
),LevelHire AS
(
        SELECT EmpId, ManagerId,Level,Value
        FROM EmployeeTable
        WHERE EmpId = 2
        UNION ALL
        SELECT RC.EmpId,  RC.ManagerId, Lh.Level,LH.Value
        FROM LevelHire LH
        INNER JOIN [EmployeeTable] RC
        ON LH.EmpId= RC.ManagerId
)
SELECT  E.EmpId, E.ManagerId,E.Level,ISNULL(E.Value ,LH.Value) Value
FROM EmployeeTable E
    LEFT JOIN LevelHire LH
    ON E.EmpId = LH.EmpId