Overview

• A CTE can be thought of as a temporary result set that is defined within the execution scope of a single SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement.
• A 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.
• Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.
• A CTE can be used to:
  1. Create a recursive query.
  2. Substitute for a view when the general use of a view is not required; that is, you do not have to store the definition in metadata.
  3. Enable grouping by a column that is derived from a scalar subselect, or a function that is either not deterministic or has external access.
  4. Reference the resulting table multiple times in the same statement.
• CTEs can be defined in user-defined routines, such as functions, stored procedures, triggers, or views.

WITH Sales
(
	[SalesPersonID],
	[SalesOrderID], 
	[SalesYear]
)
AS
(
    SELECT 
			[SalesPersonID], 
			[SalesOrderID],
			YEAR([OrderDate])
    FROM Sales.SalesOrderHeader
    WHERE SalesPersonID IS NOT NULL
)
SELECT 
	[SalesPersonID],
	COUNT([SalesOrderID]) AS [TotalSales], 
	[SalesYear]
FROM [Sales]
GROUP BY [SalesYear], [SalesPersonID]
ORDER BY [SalesYear], [SalesPersonID];

Multiple CTE in a Query

WITH Sales_CTE (SalesPersonID, TotalSales, SalesYear)  
AS  
(  
    SELECT SalesPersonID, SUM(TotalDue) AS TotalSales, YEAR(OrderDate) AS SalesYear  
    FROM Sales.SalesOrderHeader  
    WHERE SalesPersonID IS NOT NULL  
       GROUP BY SalesPersonID, YEAR(OrderDate)
),
Sales_Quota_CTE (BusinessEntityID, SalesQuota, SalesQuotaYear)  
AS  
(  
       SELECT BusinessEntityID, SUM(SalesQuota)AS SalesQuota, YEAR(QuotaDate) AS SalesQuotaYear  
       FROM Sales.SalesPersonQuotaHistory  
       GROUP BY BusinessEntityID, YEAR(QuotaDate)  
)  
SELECT SalesPersonID, 
	SalesYear,
	FORMAT(TotalSales,'C','en-us') AS TotalSales,
	SalesQuotaYear,
	FORMAT (SalesQuota,'C','en-us') AS SalesQuota,
	FORMAT (TotalSales -SalesQuota, 'C','en-us') AS Amt_Above_or_Below_Quota
FROM Sales_CTE  
JOIN Sales_Quota_CTE 
	ON Sales_Quota_CTE.BusinessEntityID = Sales_CTE.SalesPersonID  
    AND Sales_CTE.SalesYear = Sales_Quota_CTE.SalesQuotaYear  
ORDER BY SalesPersonID, SalesYear;

Recursive Queries Using CTE

WITH [NumberList]
AS
(
	SELECT 1 AS [Num] -- Anchor member
	UNION ALL
  SELECT [Num] + 1 -- Recursive Member
	FROM [NumberList]
	WHERE [Num] < 50 -- Terminator
)
SELECT [Num]
FROM [NumberList]
OPTION (MAXRECURSION 50);

Find Product Component Level using Recursive CTE

WITH Parts
(AssemblyID, ComponentID, PerAssemblyQty, EndDate, ComponentLevel) 
AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty, b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800 AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty, [bom].EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS [bom]
    INNER JOIN Parts AS p
        ON [bom].ProductAssemblyID = p.ComponentID
        AND [bom].EndDate IS NULL
)
SELECT AssemblyID, ComponentID, [Name], [PerAssemblyQty], EndDate,
        ComponentLevel 
FROM Parts AS p
INNER JOIN Production.Product AS pr
    ON p.ComponentID = pr.ProductID
ORDER BY ComponentLevel, AssemblyID, ComponentID;

Update Table Using Recursive CTE

WITH Parts
(
	AssemblyID,
	ComponentID,
	PerAssemblyQty,
	EndDate,
	ComponentLevel
)
AS
(
    SELECT b.ProductAssemblyID, b.ComponentID, b.PerAssemblyQty,
        b.EndDate, 0 AS ComponentLevel
    FROM Production.BillOfMaterials AS b
    WHERE b.ProductAssemblyID = 800
          AND b.EndDate IS NULL
    UNION ALL
    SELECT bom.ProductAssemblyID, bom.ComponentID, p.PerAssemblyQty,
        bom.EndDate, ComponentLevel + 1
    FROM Production.BillOfMaterials AS bom 
        INNER JOIN Parts AS p
        ON bom.ProductAssemblyID = p.ComponentID
        AND bom.EndDate IS NULL
)
UPDATE Production.BillOfMaterials
SET PerAssemblyQty = c.PerAssemblyQty * 2
FROM Production.BillOfMaterials AS c
JOIN Parts AS d ON c.ProductAssemblyID = d.AssemblyID
WHERE d.ComponentLevel = 0;

Guidelines for Creating and Using CTE

• A CTE must be followed by a single SELECT, INSERT, UPDATE, or DELETE statement that references some or all the CTE columns.
  • A CTE can also be specified in a CREATE VIEW statement as part of the defining SELECT statement of the view.
• Multiple CTE query definitions can be defined in a non-recursive CTE.
  • The definitions must be combined by one of these set operators: UNION ALL, UNION, INTERSECT, or EXCEPT.
• A CTE can reference itself and previously defined CTEs in the same WITH clause.
  • Forward referencing is not allowed.
• Specifying more than one WITH clause in a CTE is not allowed.
  • For example, if a CTE_query_definition contains a subquery, that subquery cannot contain a nested WITH clause that defines another CTE.