Export (0) Print
Expand All
51 out of 59 rated this helpful - Rate this topic

Using Common Table Expressions


A CTE can be used to:




  • Reference the resulting table multiple times in the same statement.




The basic syntax structure for a CTE is:



( CTE_query_definition )


The statement to run the CTE is:

SELECT <column_list>

FROM expression_name;


The following example shows the components of the CTE structure: expression name, column list, and query. The CTE expression Sales_CTE has three columns (SalesPersonID, NumberOfOrders, and MaxDate) and is defined as the total number of sales orders and the most recent sales order date in the SalesOrderHeader table for each salesperson. When the statement is executed, the CTE is referenced two times: one time to return the selected columns for the salesperson, and again to retrieve similar details for the salesperson's manager. The data for both the salesperson and the manager are returned in a single row.

USE AdventureWorks;
WITH Sales_CTE (SalesPersonID, NumberOfOrders, MaxDate)
    SELECT SalesPersonID, COUNT(*), MAX(OrderDate)
    FROM Sales.SalesOrderHeader
    GROUP BY SalesPersonID
SELECT E.EmployeeID, OS.NumberOfOrders, OS.MaxDate,
    E.ManagerID, OM.NumberOfOrders, OM.MaxDate
FROM HumanResources.Employee AS E
    JOIN Sales_CTE AS OS
    ON E.EmployeeID = OS.SalesPersonID
    ON E.ManagerID = OM.SalesPersonID
ORDER BY E.EmployeeID;

Here is a partial result set:

EmployeeID  NumberOfOrders MaxDate  ManagerID NumberOfOrders MaxDate
268         48             2004-06-01 273       NULL           NULL
275         450            2004-06-01 268       48             2004-06-01
276         418            2004-06-01 268       48             2004-06-01
277         473            2004-06-01 268       48             2004-06-01
Did you find this helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

© 2014 Microsoft. All rights reserved.