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 (SalesPers