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',
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
PIVOT(Sum(Total) FOR [Year] IN(, , , )) 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.