Microsoft® SQL Server 2012 Unleashed

Common Table Expressions

A common table expression (CTE) is an ANSI SQL-99 expression that produces a table that is referred to by name within the context of a single query. The general syntax for a CTE is as follows:

Click here to view code image

[;]WITH expression_name [ ( column_name [ ,...n ] ) ]    AS ( CTE_query_definition )

The WITH clause, in effect, defines a table and its columns. Note that the syntax of the WITH clause is similar to that of a view. You can think of a CTE as a temporary view that lasts only for the life of the query that defines the CTE. Listing 45.20 shows an example of a simple CTE. This CTE is used to return the average and maximum order subtotals for each customer. The CTE is then joined to the SalesOrderHeader ...