Microsoft® SQL Server 2008 R2 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:

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 43.34 shows an example of a simple CTE. This CTE is used to return the average and maximum sales quantities for each store. The CTE is then joined to the sales table to return the average ...