SQL Server PIVOT
This article will give a complete overview of using the PIVOT and UNPIVOT operators in SQL Server. The PIVOT and UNPIVOT operators are similar to the relational operators that allow for transforming the table-valued expression into another table. Both operators generate multidimensional reporting that helps to combine and compare a large amount of data quickly.
We can use the PIVOT operator when we need to transform table-valued expressions. It splits the unique values from one column into many columns in the final result. It also aggregates the remaining column values required in the final result. UNPIVOT operator converts data from columns of a table-valued expression into column values, which is the inverse of PIVOT.
Let us understand it with the help of the simple diagram given below:
On the left-hand side of this figure, we can see the original dataset, which has three columns: Year, Region, and Sales. Next, we can see the PIVOT table on the right-hand side, which is constructed by rotating the Region (rows) into North and South (columns). After converting rows into columns, we can do an aggregate of Sales column values for each intersection between the columns and rows of the PIVOT table.
Let us first create a table named pivot_demo to demonstrate the PIVOT and UNPIVOT operators. The following statement creates a new table in our specified database:
Next, insert some data into this table as below:
We can verify the data using the SELECT statement. We will get the below output:
This operator is used to rotate table-valued expressions. It was first introduced in SQL Server 2005 version. It converts data from rows to columns. It splits the unique values from one column into many columns and then aggregates the remaining column values required in the final result.
We must follow the following steps to create a PIVOT table:
The following syntax illustrates the use of PIVOT in SQL Server:
If we break this script, we can see that it has two separate sections. The first section selects data from the main table, and the second section determines how the PIVOT table will be constructed. The second part also contains some special keywords such as SUM, FOR, and IN. Let us see the meaning of these keywords in the PIVOT operator.
This operator is used to aggregate the values from the specified column to be used in the PIVOT table. We must use it with the PIVOT operator to get the aggregated column displays for the values sections.
This keyword is used for the PIVOT table statement to instruct the PIVOT operator on which column the PIVOT function should be applied. Basically, it indicates the column names that will transform from rows to columns.
This keyword lists all the unique values from the PIVOT column to be displayed as the columns of the PIVOT table.
Let us understand it with the help of various examples.
1. The following statement first selects the Year, North, and South column as the base data for pivoting. Then, create a temporary result using the derived table and finally apply the PIVOT operator to generate the final output. This output is also ordered in the ascending Year.
Executing this statement will produce the below output. Here, we can see the calculated sum of North and South region sales corresponding to the year values.
2. This is another example where we are going to calculate the sum of sales for each Year corresponding to the region values:
Executing this statement will produce an error because we cannot specify the numeric value as a column name directly.
However, SQL Server allows us to avoid this problem by using the brackets before each integer value. The updated statement is shown in the following code snippet:
This statement executed successfully and display the calculated sum of sales for each year corresponding to the region values:
3. The previous example of getting a PIVOT table is helpful when we are aware of all possible PIVOT column values. But suppose in the upcoming year, the number of columns is increased. Considering the preceding example, we have 2010, 2011, and 2012 years as PIVOT columns. However, there is no guarantee that these columns will not alter in the future. What happens if we have data from 2013 or 2014, or maybe even more? In such cases, we will need to use dynamic PIVOT table queries to fix this problem.
The dynamic PIVOT table query encapsulates the entire PIVOT script in a stored procedure. This procedure will provide adjustable options, allowing us to modify our requirements by changing a few parameterized values.
The following SQL code explains the working of the dynamic PIVOT table. In this script, we have first retrieved all distinct values from the PIVOT column and then write an SQL statement for executing with the PIVOT query at run time. Let's see the output after executing this script:
In this script, we have created two parameterized variables. Its description is given below:
@PivotColumn: This variable will take the column's name from the original table on which the PIVOT table is created. For example, here, the "Region" column displays all the regions available in the columns.
@PivotList: This variable will take the column list that we want to display as an output column in the PIVOT table.
Execution of Dynamic Stored Procedure
After the successful creation of the dynamic stored procedure, we are ready to execute it. The following statement is used to call the dynamic stored procedure to display the PIVOT table at run time:
Here we have now specified the column name "Region" as the first parameter and the PIVOT column list as the second parameter. Executing the script will display the following output:
Now, we can add more columns in the future at runtime to display the PIVOT table, which is not possible with the first two examples.
It is the reverse method of the PIVOT operator in SQL Server. It mans this operator performs the opposite operation of PIVOT by converting data from columns to rows. UNPIVOT operator also rotates the PIVOT table into the regular table. It was first introduced in SQL Server 2005 version.
The following syntax illustrates the UNPIVOT in SQL Server:
Let us understand how to UNPIVOT the PIVOT operation with examples. We will first create an original table and PIVOT table and then applied UNPIVOT operator on this table.
The following code snippet first declares a temporary table variable @Tab:
Next, we will insert values into this table as below:
Now, we can perform UNPIVOT operation using the below statement:
Executing the code snippet will return the following output:
The below code snippet is another example to first performs PIVOT operation and then UNPIVOT operation on the same table within a single query:
Executing the code snippet will display the same output:
NOTE: The UNPIVOT process is a reverse operation of the PIVOT procedure, but it is not an exact reversal. Because rows have been merged when PIVOT calculates the aggregate and combined many rows into a single row in the result, therefore, UNPIVOT operation cannot make the table as like the original. However, if the PIVOT operator does not merge many rows into a single row, then UNPIVOT operator can get the original table from the PIVOT output.
This article will give a complete overview of PIVOT and UNPIVOT operators in SQL Server and convert a table expression into another. It should never forget that UNPIVOT is the reverse operation of PIVOT, but it's not the exact inverse of the PIVOT result.