Return a Row

Listing 4. This T-SQL aggregate query returns a row for the four quarters of 2001 and each of the 12 Midwestern states in which the fictitious OakLeaf Consumer Electronics organization does business.

SELECT TOP 100 PERCENT State, DATENAME(year, OrderDate) + 'Q' + DATENAME(quarter, OrderDate) AS Header, 
   COUNT(Orders.OrderID) AS Orders, ROUND(SUM(Quantity * UnitPrice), 0) AS Amount, 
   DATEPART(quarter, OrderDate) AS Period, DATEPART(year, OrderDate) AS Year 
FROM Customers 
   INNER JOIN Orders ON Customers.CustID = Orders.CustID 
   INNER JOIN LineItems ON Orders.OrderID = LineItems.OrderID 
WHERE (OrderDate BETWEEN '1/1/2001 00:00:00 AM' AND '12/31/2001 11:59:59 PM') AND LineItems.Deleted = 0 
GROUP BY State, DATEPART(quarter, OrderDate), DATEPART(year, OrderDate), 
   DATENAME(year, OrderDate) + 'Q' + DATENAME(quarter, OrderDate) 
ORDER BY State, DATEPART(quarter, OrderDate), DATEPART(year, OrderDate), 
   DATENAME(year, OrderDate) + 'Q' + DATENAME(quarter, OrderDate)