Sunday, 31 May 2015

SQL Server Important and common interview queries

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-

With CTE
Select E1.EmployeeID,
E1.FirstName, E1.LastName,
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,
 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

FirstName + ' ' + LastName as [Employee Name]
,ManagerF + ' ' + ManagerL  as [Manager Name]

, EmpLevel  from CTE


  1. Please put your explanations. What is the logic behind the CTE.

    1. Thank you Mukesh for your valuable comment.

      Please 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.