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 – http://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.

TransactionDetails table Pivoting

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.

Pivoting Data report with total sales

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.

 

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.

 

Output Pivoting Table

Figure 3

 

We’ll now use the PIVOT operator to get the same result.

The query can be split into 4 parts,

  1. Select the Non-Pivot and Pivot Columns. Pivot columns are the row values in the YEAR column in our case (2016…2009)
  2. 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.
  3. This is where we apply the Pivot Operator
    1. 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)
    2. Specify the column containing the rows that will become the column headers in the result set. In our case, it’s the YEAR column.
    3. 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.

The query will give you the execution plan for pivot query, as shown below.

execution plan for pivot query Pivoting Data

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.

 

The above query creates a comma delimited list of all values which are there in YEAR column in TransactionDetails table.

 

TransactionDetails table Pivoting Data

Figure 5

We can now construct a dynamic pivot query, specifying @pivotcols variable wherever pivot columns are required, as shown below.

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!!!

Thanks,

Ahmad

Like us on FaceBook | Join me on LinkedIn
Follow me on Twitter | Follow me on FaceBook