PIVOT Data in Yukon (RIP Case and Rozenshtein)
Let’s face it, users usually want to see data in tabular format, which is a bit of a challenge when we usually store data in a highly relational form. PIVOT is a new TSQL operator that you can specify in your FROM clause to rotate rows into columns and create a traditional “Crosstab” query without using CASE, Rozenshtein or subqueries. You have always been able to do this easily in Access with Pivot and Transform, but SQL Server was always a step behind.
Using PIVOT is easy. First in your select statement you need to specify the values you want to pivot on, in the following example, we will use the Year of the Order. Our FROM clause looks normal except for the PIVOT part. The PIVOT statement creates the value we plan on showing in the rows of the newly created columns, in this case I am using the aggregate SUM of the TotalAmount (a calculated field in our FROM clause). Then we have to use the FOR operator to list the values we are going to Pivot on in the OrdersYear column. The example is shown here:
SELECT CustomerID, [1996] AS 'Y1996', [1997] AS 'Y1997', [1998] AS 'Y1998', [1999] as 'Y1999'
FROM
(SELECT CustomerID,OD.UnitPrice * OD.Quantity - OD.Discount AS TotalAmt,
Year(dbo.Orders.OrderDate) as OrdersYear
FROM dbo.Orders INNER JOIN dbo.[Order Details] As OD
ON dbo.Orders.OrderID = OD.OrderID)
As Orders
PIVOT
(
SUM(TotalAmt)
FOR OrdersYear IN([1996], [1997], [1998], [1999])
) AS XTabData
Order BY CustomerID
The results look like this:
|
CustomerID |
Y1996 |
Y1997 |
Y1998 |
|
ALFKI |
NULL |
2293.25 |
2301.9 |
|
ANATR |
88.8 |
799.75 |
514.4 |
|
ANTON |
403.2 |
6451.15 |
660 |
|
AROUT |
1379 |
6588.4 |
5838.4 |
|
BERGS |
4324.4 |
14532.25 |
8108.5 |
|
BLAUS |
NULL |
1079.8 |
2160 |
|
BLONP |
9986.2 |
8371.05 |
730 |
|
BOLID |
982 |
4035.3 |
279.8 |
|
BONAP |
4202.35 |
12460.7 |
7184.7 |
That is all there is to it. Of course this is a real simple example to show you the new concept, you can then of course get more sophisticated aggregates and even use Common Table Expressions in the FROM clause. Also you can use the UNPIVOT operator to normalize data that is already pivoted.
See my article on SQLJunkies.com.