business intelligence consulting
 

Using PIVOT operator in SQL Server

by Hans Esquivel 18. July 2013 12:50

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!

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.

SQL Server | Reporting Services

About Us

InfoToad Consulting provides Data Management and Business Intelligence Solutions and Services. If you would like to learn more about our services or solutons, please visit us at http://www.infotoad.com or call us at (877) 488-0566.

Month List

Tag cloud