Sql server® 2012 t-sql fundamentals

assigning Column aliases in CTes

CTEs also support two forms of column aliasing—inline and external. For the inline form, specify 

AS ; for the external form, specify the target column list in parentheses 

immediately after the CTE name.

Here’s an example of the inline form.

WITH C AS 

( 

  SELECT YEAR(orderdate) AS orderyear, custid 

  FROM Sales.Orders 

) 

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 

FROM C 

GROUP BY orderyear;

And here’s an example of the external form.

WITH C(orderyear, custid) AS 

( 

  SELECT YEAR(orderdate), custid 

  FROM Sales.Orders 

) 

SELECT orderyear, COUNT(DISTINCT custid) AS numcusts 

FROM C 

GROUP BY orderyear;

The motivations for using one form or the other are similar to those described in the context of 

derived tables.

www.it-ebooks.info

---