PIVOT In SQL
Pivot In Sql Server
With SQL Server 2005 a lot of new features have been introduced. One of these new features is PIVOT. What this allows you to do is to turn query results on their side, so instead of having results listed down like the listing below, you have results listed across.
|
SalesPerson |
Product |
SalesAmount |
|
Bob |
Pickles |
$100.00 |
|
Sue |
Oranges |
$50.00 |
|
Bob |
Pickles |
$25.00 |
|
Bob |
Oranges |
$300.00 |
|
Sue |
Oranges |
$500.00 |
With a straight query the query results would be listed down, but the ideal solution would be to list the Products across the top for each SalesPerson, such as the following:
|
SalesPerson |
Oranges |
Pickles |
|
Bob |
$300.00 |
$125.00 |
|
Sue |
$550.00 |
|
To use PIVOT you need to understand the data and how you want the data displayed. First you have the data rows, such as SalesPerson and the columns, such as the Products and then the values to display for each cross section. Here is a simple query that allows us to pull the cross-tab results.
SELECT SalesPerson, [Oranges] AS Oranges, [Pickles] AS Pickles
FROM
(SELECT SalesPerson, Product, SalesAmount
FROM ProductSales ) ps
PIVOT
(
SUM (SalesAmount)
FOR Product IN
( [Oranges], [Pickles])
) AS pvt