WITH (common_table_expression) Syntax
A common table expression has the following format:
[ WITH { common_table_expression },... ]
where the common_table_expression format is:
expr_name [ ( { column_name },... ) ] AS ( CTE_query_def )
expr_name
Is an identifier for the common table expression. The expr_name must be unique among other common table expression names defined in the same WITH common_table_expression clause, but expr_name can be the same as the name of a base table or view. Any reference to expr_name in the query uses the common table expression and not the base object.
column_name
Specifies a column name in the common table expression. Duplicate names within a single CTE definition are not allowed. The number of column names specified must match the number of columns in the result set of the CTE_query_def. The list of column names is optional only if distinct names for all resulting columns are supplied in the query expression.
CTE_query_def
Specifies a SELECT statement whose result set populates the common table expression. The SELECT statement for CTE_query_def must meet the same requirements as for creating a view, except a CTE cannot define another CTE.
If more than one CTE_query_def is defined, the queries must be joined by either UNION or UNION ALL.