Question 2: Using CTE, how to write a recursive query which will define the organization hierarchy level ?
Solution - We can achieve the above situation by the following query-
Solution - We can achieve the above situation by the following query-
With CTE
As
(
Select E1.EmployeeID,
E1.FirstName, E1.LastName,
E1.ManagerID,
CAST(''as nvarchar(30)) as [ManagerF],
CAST(''as nvarchar(40)) as [ManagerL],
1 as EmpLevel from dbo.#MyEmployees
E1 where managerId is
null
Union ALL
Select E2.EmployeeID,
E2.FirstName, E2.LastName,
E2.ManagerID,
EL.FirstName as [ManagerF],
EL.LastName as [ManagerL],
EL.EmpLevel + 1 as EmpLevel from dbo.#MyEmployees
E2
Inner Join CTE as EL on EL.EmployeeID=E2.ManagerID
where E2.ManagerID is not null
)
Select
FirstName + ' ' + LastName as
[Employee Name]
,ManagerF + ' ' + ManagerL as [Manager Name]
, EmpLevel from CTE
2 Comments
Please put your explanations. What is the logic behind the CTE.
ReplyDeleteThank you Mukesh for your valuable comment.
DeletePlease note that above explanation is regarding the interview question only which has been asked recently in one of the interview.
It may not include the complete detail on Common Table Expression.