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