In this blog post, I’ll be talking about Pivoting Data in SQL Server. Pivot refers to rotating rows into columns with or without aggregation. Let’s understand this with an example.
I’ll be using SQLMaestros database. You can download the SQLMaestros database from here – https://sqlmaestros.com/download/sqlmaestros-sample-database/
The SQLMaestros has a TransactionDetails table that records the transactions done by subscribers to purchase Hands-On-Labs subscription. The TransactionDetails table has a PaymentMethod and Subtotal column among other columns. A subscriber can make a payment using PayPal, Credit Card or offline payment methods. The SubTotal column records the total payment made for a subscription. The Year column contains the year in which the transaction is made. The data looks like as shown below.
Let’s say I have to prepare a pivot report with total sales for yeah payment method across all years. The report should be as shown in below image.
Observe, that the row values in the YEAR column in Figure 1 are the column headers in Figure 2. Also, the values against the payment method are aggregated across years and have been put under the respective PaymentMethod and YEAR column. For example, the total sales for credit card in the year 2016 is 746 + 234 (first two rows) = 980. The other values are aggregated accordingly.
Starting SQL Server 2005, you can use the PIVOT operator to get result similar to figure 2. However, we’ll first see how to do this without PIVOT operator and then with Pivot operator.
SELECT PaymentMethod , SUM(CASE YEAR(OrderDate) WHEN 2016 THEN SubTotal ELSE 0 END) AS '2016' , SUM(CASE YEAR(OrderDate) WHEN 2015 THEN SubTotal ELSE 0 END) AS '2015' , SUM(CASE YEAR(OrderDate) WHEN 2014 THEN SubTotal ELSE 0 END) AS '2014' , SUM(CASE YEAR(OrderDate) WHEN 2013 THEN SubTotal ELSE 0 END) AS '2013' , SUM(CASE YEAR(OrderDate) WHEN 2012 THEN SubTotal ELSE 0 END) AS '2012' , SUM(CASE YEAR(OrderDate) WHEN 2011 THEN SubTotal ELSE 0 END) AS '2011' , SUM(CASE YEAR(OrderDate) WHEN 2010 THEN SubTotal ELSE 0 END) AS '2010' , SUM(CASE YEAR(OrderDate) WHEN 2009 THEN SubTotal ELSE 0 END) AS '2009' FROM hol.TransactionDetails GROUP BY PaymentMethod;
The above query, uses CASE statement to sum up the values for each year grouping the result on PaymentMethod column. This produces the output similar to figure 2, as shown below.
We’ll now use the PIVOT operator to get the same result.
-- Pivot with Pivot keyword SELECT /* Part 1: Specify the non-pivot and pivot cols*/ PaymentMethod , /* pivot cols */