Using PIVOT operator in SQL Server

In this article we'll show you how to use SQL Server PIVOT operator. The PIVOT operator is useful when you want to generate cross-tabular views to summarize data.

What is PIVOT?

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output, and performs aggregations where they are required on any remaining column values that are wanted in the final output. UNPIVOT performs the opposite operation to PIVOT by rotating columns of a table-valued expression into column values.

Scripting the Code

We'll begin first with defining a common row-level query to capture total sales by year and quarter.

SELECT Year([OrderDate]) as 'Year', 'Q' + DateName(QUARTER, [OrderDate]) as 'Quarter',

Sum([SubTotal]) as'Total'

FROM [Sales].[SalesOrderHeader] soh

GROUP BY Year([OrderDate]), DateName(QUARTER, [OrderDate])

ORDER BY Max([OrderDate]); 

 

Now we use the PIVOT operator to generate a cross-tabular view of the data by rotating the Year to a column value and Quarter to row-level values.

WITH X AS

(

SELECT Year([OrderDate]) AS [Year], 'Q' + DateName(QUARTER, [OrderDate]) AS [Quarter], [SubTotal] AS Total

FROM [Sales].[SalesOrderHeader]

)

SELECT *

FROM X

PIVOT(Sum(Total) FOR [Year] IN([2005], [2006], [2007], [2008])) AS Y            

ORDER BY Y.Quarter ASC

 

As you can see, we now have the Quarterly aggregated Sales Amounts by Year.

Note: You can call this query from Excel, SQL Server Reporting Service, or some other reporting tool to format the data.

 

Cheers!

Add comment