Get all child or sub employee with its Level by using CTE

Get all child employee with its Level by using CTE

CTE stand for Common Table Expression

CTE use for get recursive data

here we will learn how get all child employee recursively and also get its employment level

Here i have usermaster table with structure Like

Column_name       Type

UserID                   int

UserName              nvarchar(100)

Password               nvarchar(400)

FirstName              nvarchar(40)

LastName              nvarchar(40)

Surname                nvarchar(100)

Mobile                   nvarchar(32)

Email                    nvarchar(100)

ParentUserID         int

-------------------------
Example :
-------------------------
;with cte(UserID,FirstName,ParentUserID,[Level])
as(
select UserID,FirstName,ParentUserID,0 as [Level] from usermaster where UserID = 1
union all
select u.UserID,u.FirstName,u.ParentUserID, c.[Level]+1 as [Level] from UserMaster u inner join cte c on c.UserID=u.ParentUserID
)
select * from cte
---------------------------
output
---------------------------

UserID  FirstName           ParentUserID    Level

1              Jayendra              0                              0

2              aaa                       1                             1

3              bbb                      1                             1

4              ccc                       1                             1

13           chaa                      1                             1

14           ccc                        1                             1

16           jay                         3                            2

12           chaa                      2                             2

15           chaa                      2                             2