The basic syntax structure for a CTE is:
WITH expression_name [ ( column_name [,...n] ) ]
( 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:
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
CREATE TABLE dbo.Employees
EmployeeID int NOT NULL PRIMARY KEY,
FirstName varchar(50) NOT NULL,
LastName varchar(50) NOT NULL,
ManagerID int NULL
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.
cteEmployeeHierarchy (EmployeeID, FirstName, LastName, ManagerID, EmployeeLevel)
SELECT EmployeeID, FirstName, LastName, ManagerID, 1
WHERE ManagerID IS NULL
SELECT e.EmployeeID, e.FirstName, e.LastName, e.ManagerID,
cteEH.EmployeeLevel + 1
FROM Employees e
INNER JOIN cteEmployeeHierarchy cteEH
ON e.ManagerID = cteEH.EmployeeID
FirstName + ' ' + LastName AS FullName,
(SELECT FirstName + ' ' + LastName FROM Employees
WHERE EmployeeID = cteEmployeeHierarchy.ManagerID) AS Manager
ORDER BY EmployeeLevel , ManagerID
The CTE has enormous capabilities to take over any complex situation.