Writing Database Queries - Beginning C# 2008 Databases: From Novice to Professional - page 45

PIVOT Operator
A common scenario where PIVOT can be useful is when you want to generate cross-
tabulation reports to summarize data. The PIVOT operator can rotate rows to columns.
For example, suppose you want to query the Sales.CreditCard table in the Adventure-
Works database to determine the number of credit cards of a particular type that will be
expiring in specified year.
If you look at the query for GROUP BY mentioned in the previous section and shown
earlier in Figure 4-4, the years 2006 and 2007 have also been passed to the WHERE clause,
but they are displayed only as part of the record and get repeated for each type of card
separately, which has increased the number of rows to eight. PIVOT achieves the same
goal by producing a concise and easy-to-understand report format.
Try It Out: Using the PIVOT Operator
The Sales.CreditCard table contains the details for customers' credit cards. You need to
total the cards of a specific type that will be expiring in a particular year.
Open a New Query window in SQL Server Management Studio Express. Enter the fol-
lowing query and click Execute. You should see the results shown in Figure 4-5.
Use AdventureWorks
Go
select CardType ,[2006] as Year2006,[2007] as Year2007
from
(
select CardType,ExpYear
from Sales.CreditCard
)piv Pivot
(
count(ExpYear) for ExpYear in ([2006],[2007])
)as carddetail
order by CardType