Common Table Expressions (CTE)

Common Table Expressions (CTE)

The basic syntax for a CTE utilizes the WITH keyword followed by a name and definition:

WITH <CTE name> [( <column name> [,...n])]

AS

( <query returning tabular data> )

<statement that will make use of the CTE>

After the CTE is defined, you can refer to it by name just as if it were a table.

WITH Report (ManagerID,EmployeeID,EmployeeName,Label)

AS

(SELECT e.ManagerID,e.EmployeeID,e.EmployeeName,1 as Label

from Employee e

WHERE e. ManagerID is null

UNION ALL

SELECT e.ManagerID,e.EmployeeID,e.EmployeeName,r.Label+1 as  Label

from Employee e JOIN Report r ON e.ManagerID=r.EmployeeID)

SELECT ManagerID,EmployeeID,EmployeeName,Label FROM Report

Output Come like………

ManagerID	EmployeeID	EmployeeName	Label
NULL	1	MD Sir	1
1	2	Head Finance	2
1	3	Head IT	2
3	4	Programmer	3