More on pivot tables - adding row totals to a pivot table

Below is a query that retrieves monthly order count for each customer and displays it in tabular form using pivot table. Second column displayed is the row total for all the months for the current year.

As an example I used Northwind database.

SELECT CompanyName, ISNULL([Jan],0) + ISNULL([Feb],0) + ISNULL([Mar],0) + ISNULL([Apr],0) + ISNULL([May],0) + ISNULL([Jun],0) + ISNULL([Jul],0) + ISNULL([Aug],0) + ISNULL([Sep],0) + ISNULL([Oct],0) + ISNULL([Nov],0) + ISNULL([Dec],0) As [Year Total],[Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec] 
FROM (SELECT c.CompanyName,c.CustomerID,o.OrderID, SUBSTRING(DateName(MONTH,o.OrderDate), 0,4) As OrderMonth FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID WHERE YEAR(OrderDate)=Year(GETDATE())) Data
PIVOT(
COUNT(OrderID) 
FOR OrderMonth IN([Jan], [Feb], [Mar], [Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])) As MonthlyOrderQty

The result displayed will be something like this:

Company Name	Year Total	Jan	Feb	Mar	Apr	May	Jun	Jul	Aug	Sep	Oct	Nov	Dec
Alfreds Futterkiste	3	1	0	1	1	0	0	0	0	0	0	0	0
Ana Trujillo Emparedados y helados	1	0	0	1	0	0	0	0	0	0	0	0	0
Antonio Moreno Taquería	1	1	0	0	0	0	0	0	0	0	0	0	0
Around the Horn	4	0	1	2	1	0	0	0	0	0	0	0	0
Berglunds snabbköp	5	2	2	1	0	0	0	0	0	0	0	0	0
Blauer See Delikatessen	3	1	0	1	1	0	0	0	0	0	0	0	0
Blondesddsl père et fils	1	1	0	0	0	0	0	0	0	0	0	0	0

Pivot tables in SQL Server 2008

A pivot table is a frequently used method of summarizing and displaying especially report data by means of grouping and aggregating values.
For example, given the data from Orders table - OrderID, CustomerID, OrderDate etc., I can create a pivot table to display number of orders per each year listed in the database:

SELECT * FROM (SELECT OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce something like that:

2008	2009	2010
152	408	270

which would be quite difficult to accomplish without the use of pivot table.

SELECT * FROM (SELECT CustomerID, OrderID, YEAR(OrderDate) As OrderYear FROM Orders) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

will produce a list of customers with number of orders for each customer per each year:

CustomerID	2008	2009	20120
ALFKI	0	3	3
ANATR	1	2	1
ANTON	1	5	1
AROUT	2	7	4
BERGS	3	10	5
BLAUS	0	4	3
BLONP	3	7	1
BOLID	1	1	1

The query below is similar to the one just above but it returns Company name instead of Customer ID

SELECT * FROM (
SELECT c.CompanyName, o.OrderID, YEAR(o.OrderDate) As OrderYear FROM Orders as o INNER JOIN Customers as c ON o.CustomerID=c.CustomerID
) Data
PIVOT(
COUNT(OrderID) 
FOR OrderYear IN([2008], [2009], [2010])) As YearlyOrderQty

CompanyName	2008	2009	2010
Alfreds Futterkiste	0	3	3
Ana Trujillo Emparedados y helados	1	2	1
Antonio Moreno Taquería	1	5	1
Around the Horn	2	7	4
Berglunds snabbköp	3	10	5
Blauer See Delikatessen	0	4	3
Blondesddsl père et fils	3	7	1
Bólido Comidas preparadas	1	1	1