Using Common Table Expressions (CTE) in SQL Server

The Common Table Expression (CTE) is called as a temporary named RESULT SET, within the scope of an executing statement that can be used within a SELECT, INSERT, UPDATE, or DELETE or even in a CREATE VIEW or MERGE statement. CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. CTE Syntax:

The basic syntax structure for a CTE is:

WITH expression_name [ ( column_name [,...n] ) ]

AS

( CTE_query_definition )

The list of column names is optional only if distinct names for all resulting columns are supplied in the query definition.

The statement to run the CTE is:

SELECT

FROM expression_name;

Simple CTE Example:

To get the product details from the ‘product’ table.

WITH product_CTE
AS
(
   SELECT ProductName, Price, ManufactureDate,ExpirayDate
   FROM dbo.Products

)SELECT * FROM product_CTE;

Recursive CTE Example:

A recursive CTE is one that references itself within that CTE. The recursive CTE is useful when working with hierarchical data because the CTE continues to execute until the query returns the entire hierarchy.

A typical example of hierarchical data is a table that includes a list of employees. For each employee, the table provides a reference to that person’s manager. That reference is itself an employee ID within the same table. You can use a recursive CTE to display the hierarchy of employee data, as it would appear within the organizational chart.

To demonstrate how the recursive CTE works, I used the following Transact-SQL statements to create and populate the Employees table.

IF OBJECT_ID('Employees', 'U') IS NOT NULL

DROP TABLE dbo.Employees

GO

CREATE TABLE dbo.Employees

(

EmployeeID int NOT NULL PRIMARY KEY,

FirstName varchar(50) NOT NULL,

LastName varchar(50) NOT NULL,

ManagerID int NULL

)

GO

INSERT INTO Employees VALUES (101, 'Hiren', 'Khirsaria', NULL)

INSERT INTO Employees VALUES (102, 'Dhaval', 'Dhamsaniya', 101)

INSERT INTO Employees VALUES (103, 'Dharmesh', 'Barochia', 101)

INSERT INTO Employees VALUES (104, 'Chirag', 'Patel', 102)

INSERT INTO Employees VALUES (105, 'Jack', 'Mack', 102)

INSERT INTO Employees VALUES (106, 'Jossef', 'Goldberg', 103)

INSERT INTO Employees VALUES (107, 'Pavan', 'Patel', 103)

INSERT INTO Employees VALUES (108, 'Chetan', 'Virparia', 105)

INSERT INTO Employees VALUES (109, 'Boogi', 'Woogi', 105)

INSERT INTO Employees VALUES (110, 'Abc', 'Xyz', 106)

Now Create CTE for Employee table with select statement to dispaly result of manager for each employee list.

WITH

cteEmployeeHierarchy (EmployeeID, FirstName, LastName, ManagerID, EmployeeLevel)

AS

(

SELECT EmployeeID, FirstName, LastName, ManagerID, 1

FROM Employees

WHERE ManagerID IS NULL

UNION ALL

SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,

cteEH.EmployeeLevel + 1

FROM Employees e

INNER JOIN cteEmployeeHierarchy cteEH

ON e.ManagerID = cteEH.EmployeeID

)

SELECT

FirstName + ' ' + LastName AS FullName,

EmployeeLevel ,

(SELECT FirstName + ' ' + LastName FROM Employees

WHERE EmployeeID = cteEmployeeHierarchy.ManagerID) AS Manager

FROM cteEmployeeHierarchy

ORDER BY EmployeeLevel , ManagerID

OUTPUT :

The CTE has enormous capabilities to take over any complex situation.