Figure 6 A Non-recursive CTE
　

USE AdventureWorks

DECLARE @CustomerID AS INT
SET @CustomerID = 3

WITH YearlyCustOrders(OrderYear, TotalValue)
AS
(
  SELECT YEAR(OrderDate), SUM(OrderQty*UnitPrice)
  FROM SalesOrderHeader AS H
    JOIN SalesOrderDetail AS D
      ON H.SalesOrderID = D.SalesOrderID
  WHERE CustomerID = @CustomerID
  GROUP BY YEAR(OrderDate)
)
SELECT * FROM YearlyCustOrders;