Hi Friends,
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.
Figure 1
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.
Figure 2
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.
Figure 3
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 */
[2016] ,
[2015] ,
[2014] ,
[2013] ,
[2012] ,
[2011] ,
[2010] ,
[2009]
/* Part 2: The data to apply pivot on */
FROM ( SELECT PaymentMethod ,
SubTotal ,
YEAR(OrderDate) AS [YEAR]
FROM [SQLMaestros].[hol].[TransactionDetails]
) AS sq
/* Part 3: Apply Pivot */
PIVOT(
/* Part 3a: apply the aggregation*/ SUM(SubTotal)
FOR
/* Part 3b: column containing values that will become column headers*/
[YEAR]
IN
/* Part 3c: pivoted columns - row values in [YEAR] column which will be columns in output */
( [2016],
[2015], [2014],
[2013], [2012],
[2011], [2010],
[2009]
) ) AS Pvt;
The query can be split into 4 parts,
- Select the Non-Pivot and Pivot Columns. Pivot columns are the row values in the YEAR column in our case (2016…2009)
- Define the data on which pivot is to be performed. This is where we select the columns required in result set and the pivot column.
- This is where we apply the Pivot Operator
- Specify the aggregation. In our case, we need to sum total case across all payment method and across all years. So it’s SUM(SubTotal)
- Specify the column containing the rows that will become the column headers in the result set. In our case, it’s the YEAR column.
- Specify the pivoted columns. These should be same as specified in Part 1, pivot columns.
Comparing the two solutions, CASE and PIVOT operator, the solution with Pivot operator is more readable and short. However, the solutions are equal in performance.
Let’s see how PIVOT operator is implemented internally.
-- how pivot operator is implemented
SET SHOWPLAN_TEXT ON
GO
SELECT PaymentMethod ,
/* specify the pivot columns headers*/
[2016] ,
[2015] ,
[2014] ,
[2013] ,
[2012] ,
[2011] ,
[2010] ,
[2009]
/* The data to apply pivot on */
FROM ( SELECT PaymentMethod ,
SubTotal ,
YEAR(OrderDate) AS [YEAR]
FROM [SQLMaestros].[hol].[TransactionDetails]
) AS sq /* apply pivot */ PIVOT(
/* apply the aggregation*/ SUM(SubTotal)
FOR
/* column containing values that will become column headers*/
[YEAR]
IN
/* pivoted columns - row values in [YEAR] column which will be columns in output */
( [2016],
[2015], [2014],
[2013], [2012],
[2011], [2010],
[2009]
) ) AS Pvt;
GO
SET SHOWPLAN_TEXT OFF
The query will give you the execution plan for pivot query, as shown below.
Figure 4
Expand the 2nd row which is the Hash Match operator. Observe, that hash match uses a similar CASE logic that we used in our first solution, the one with the CASE clause.
|–Hash Match(Aggregate, HASH:([SQLMaestros].[hol].[TransactionDetails].[PaymentMethod]), RESIDUAL:([SQLMaestros].[hol].[TransactionDetails].[PaymentMethod] = [SQLMaestros].[hol].[TransactionDetails].[PaymentMethod]) DEFINE:([Expr1043]=COUNT_BIG(CASE WHEN [Expr1002]=(2016) THEN [SQLMaestros].[hol].[TransactionDetails].[SubTotal] ELSE NULL END), [Expr1044]=SUM(CASE WHEN [Expr1002]=(2016) THEN [SQLMaestros].[hol].[TransactionDetails].[SubTotal] ELSE NULL END), [Expr1045]=COUNT_BIG(CASE WHEN [Expr1002]=(2015) THEN [SQLMaestros].[hol].[TransactionDetails].[SubTotal] ELSE NULL END), [Expr1046]=SUM(CASE
One of the constraint with PIVOT operator is that you’d have to hard code the pivot columns in the pivot query. A workaround to this is to dynamically select the pivot columns.
-- Query 1: Get pivot columns
DECLARE @pivotcols VARCHAR(MAX)= ( SELECT STUFF(( SELECT DISTINCT
',' +
QUOTENAME(CAST(YEAR(OrderDate) AS CHAR(4)),'[')
FROM hol.TransactionDetails
ORDER BY ','
+ QUOTENAME(CAST(YEAR(OrderDate) AS CHAR(4)),'[')
FOR
XML PATH('')
), 1, 1, '')
);
PRINT @pivotcolsThe above query creates a comma delimited list of all values which are there in YEAR column in TransactionDetails table.
Figure 5
We can now construct a dynamic pivot query, specifying @pivotcols variable wherever pivot columns are required, as shown below.
-- Query 2: Prepare the pivot query
DECLARE @sql VARCHAR(MAX)
SET @sql =
'
SELECT PaymentMethod ,' +
/* specify the pivot columns headers*/
@pivotcols
+ '
/* The data to apply pivot on */
FROM ( SELECT PaymentMethod ,
SubTotal ,
YEAR(OrderDate) AS [YEAR]
FROM [SQLMaestros].[hol].[TransactionDetails]
) AS sq /* apply pivot */ PIVOT(
/* apply the aggregation*/ SUM(SubTotal)
FOR
/* column containing values that will become column headers*/
[YEAR]
IN
/* pivoted columns - row values in [YEAR] column which will be columns in output */
(' + @pivotcols + '
) ) AS Pvt;'
EXECUTE(@sql)
Execute the Query 1 and Query 2 together, to get the pivot report.
The above query is similar to our 2nd solution, however, instead of hardcoding pivot column values, we have specified the @pivotcols variable.
If you print out the @sql variable, you’ll get they output similar to the solution 2.
Happy Learning!!!
Leave A Comment