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:

  CREATE TABLE pivot_demo    (       Region varchar(45),       Year int,       Sales int    )    

Next, insert some data into this table as below:

  INSERT INTO pivot_demo  VALUES (‘North’, 2010, 72500),  (‘South’, 2010, 60500),  (‘South’, 2010, 52000),  (‘North’, 2011, 45000),  (‘South’, 2011, 82500),    (‘North’, 2011, 35600),  (‘South’, 2012, 32500),   (‘North’, 2010, 20500);   

We can verify the data using the SELECT statement. We will get the below output:

PIVOT Operator

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:

Select the base dataset for pivoting.
Create temporary results with the help of a derived table or CTE (common table expression).
Make use of the PIVOT operator.

Syntax

The following syntax illustrates the use of PIVOT in SQL Server:

  SELECT <non-pivoted column>, <list of pivoted column>    FROM  (<SELECT query  to produces the data>)  AS <alias name>    PIVOT ( <aggregation function>(<column name that will aggregated>)    FOR  [<column name that  will become column headers>]        IN ( [list of  pivoted columns]) ) AS <alias name  for  PIVOT table>    <ORDER BY clause>  

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.

SUM

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.

FOR Keyword

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.

IN Keyword

This keyword lists all the unique values from the PIVOT column to be displayed as the columns of the PIVOT table.

Example

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.

  SELECT Year, North, South FROM     (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1    PIVOT    (SUM(Sales) FOR Region IN (North, South)) AS Tab2    ORDER BY Tab2.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:

  SELECT Region, 2010, 2011, 2012 FROM     (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1    PIVOT    (SUM(Sales) FOR [Year] IN (2010, 2011, 2012)) AS Tab2  ORDER BY Tab2.Region;  

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:

  SELECT Region, [2010], [2011], [2012] FROM     (SELECT Region, [Year], Sales FROM pivot_demo ) AS Tab1    PIVOT    (SUM(Sales) FOR [Year] IN ([2010], [2011], [2012])) AS Tab2  ORDER BY Tab2.Region;   

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:

  CREATE PROCEDURE DynamicPivotTable    @PivotColumn NVARCHAR(255),    @PivotList NVARCHAR(255)  AS  BEGIN   DECLARE @Query NVARCHAR(MAX);     SET @Query = N’      SELECT * FROM (SELECT [Region], [Year], [Sales] FROM pivot_demo) AS tab1      PIVOT (SUM([Sales]) FOR [‘+@Pivot_Column+’] IN (‘+@Pivot_List+’)) AS PivotTable’;    EXEC(@Query)  END   

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.

UNPIVOT Operator

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.

Syntax

The following syntax illustrates the UNPIVOT in SQL Server:

  SELECT (column_names)   FROM (table_name)   UNPIVOT   (      Aggregate_function (column to be aggregated)     FOR PivotColumn IN (pivot column values)   ) AS (alias_name)  

Example

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:

  DECLARE @Tab TABLE    (       Year int,       North varchar(45),     South varchar(45)  )  

Next, we will insert values into this table as below:

  INSERT INTO @Tab    SELECT Year, North, South FROM     (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1    PIVOT    (SUM(Sales) FOR Region IN (North, South)) AS PivotTable    ORDER BY PivotTable.Year  

Now, we can perform UNPIVOT operation using the below statement:

  SELECT Region, Year, Sales FROM @Tab t    UNPIVOT    (    Sales FOR Region IN (North, South)    ) AS UnpivotTable  

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:

  SELECT Region, Year, Sales FROM (  SELECT Year, North, South FROM     (SELECT Region, Year, Sales FROM pivot_demo ) AS Tab1    PIVOT    (SUM(Sales) FOR Region IN (North, South)) AS PivotTable    ) P  –Perform UNPIVOT Operation  UNPIVOT    (    Sales FOR Region IN (North, South)    ) AS UnpivotTable  

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.

Conclusion

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.

Next TopicEOMONTH Function in SQL Server

SQL Server PIVOT

SQL Server PIVOT

PIVOT Operator

Syntax

Example

UNPIVOT Operator

Conclusion

Add Columns in SQL Server

AVG Function in SQL

You may also like