Multiple CTEs in a Single T-SQL Statement

What is CTE

From BOL,

Common Table Expression (CTE) can be thought of as a temporary result set that is defined within the execution scope of a SELECT, INSERT, UPDATE, DELETE, or CREATE VIEW statement. A CTE is similar to a derived table in that it is not stored as an object and lasts only for the duration of the query. Unlike a derived table, a CTE can be self-referencing and can be referenced multiple times in the same query.

However, in BOL there is no example, how to write a single T-SQL statement with multiple CTEs. hence I have seen lot of queries about this in many forums. Following is the format you should use.

WITH CTEA (ID,Col1)

AS

(

Syntax for CTEA

),

CTEB (ID,Col2)

AS

(

Syntax for CTEB

)

SELECT CTEA.ID,CTEA.Col1,CTEB.Col2 FROM CTEA

INNER JOIN  CTEB

ON CTEA.ID = CTEB.ID

Notice that two CTEs are separated by , and for the second CTE you SHOULD NOT use WITH statement.

Here is an example, where you want to compare sales values between year 2003 and 2004 for each product. 

WITH Prod2003 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2003
GROUP BY Prod.ProductNumber),
Prod2004 (ProductNumber,Amount)
AS
(
SELECT Prod.ProductNumber,SUM(LineTotal)  FROM Sales.SalesOrderDetail SOD INNER JOIN
Sales.SalesOrderHeader SOH
ON SOD.SalesOrderID = SOH.SalesOrderID
INNER JOIN Production.Product Prod
ON Prod.ProductId = Sod.ProductId
WHERE YEAR(OrderDate) = 2004
GROUP BY Prod.ProductNumber
)

SELECT Prod2003.ProductNumber,
Prod2003.Amount Sales2003,
Prod2004.Amount Sales2004 ,
Prod2004.Amount - Prod2003.Amount SalesIncrement
FROM Prod2003
INNER JOIN   Prod2004
ON Prod2003.ProductNumber = Prod2004.ProductNumber

Output for the above query is displayed in following image.