In SQL Server, PIVOT and UNPIVOT operators allow you to rearrange your data in different ways. Let’s say that you have data in rows, and you want to turn that data into columns. We can use PIVOT. If you’re going to reverse the process and turn columns into rows, then we can use UNPIVOT.
In this blog, we will learn about PIVOT and UNPIVOT operators and their implementation. We will also discuss in which scenarios we need to use these operators.
Table of Contents
What is PIVOT in SQL?
The PIVOT operator is one of the most valuable features of SQL, as it helps you to turn row data into columns. It can be helpful when you want to rearrange your data in such a way that it can be easier to read, especially when we talk about reports and analysis. When you are dealing with large datasets in business intelligence, it allows you to summarize the data in a more presentable way.
PIVOT Syntax in SQL:
SELECT <columns>
FROM
(
SELECT <column1>, <column2>, <aggregation_column>
FROM <table_name>
) AS SourceTable
PIVOT
(
<aggregate_function>(<aggregation_column>)
FOR <column_to_pivot> IN (<list_of_new_columns>)
) AS PivotTable;
- Choose the table’s data and designate it as SourceTable.
- Utilize the PIVOT operator to achieve the following objectives:
- Add an aggregate function like SUM or COUNT.
- Turn distinct entries from one field into column names.
- The final result is a PivotTable, which rearranges data from rows to columns.
Pivot Example
Let’s say we have a table called SalesData where we have three columns: ProductID, Month, and SalesAmount. Here, our goal is to rearrange the data so that instead of getting the month-wise sales data in rows, we will get separate columns for each month. This allows us to analyze the sales data in a more structured way.
Creating the Table:
CREATE TABLE SalesData (
ProductID INT,
Month NVARCHAR(50),
SalesAmount DECIMAL(10, 2)
);
-- Sample Data Insertion
INSERT INTO SalesData (ProductID, Month, SalesAmount)
VALUES (1, 'January', 1000),
(1, 'February', 1200),
(1, 'March', 1500),
(2, 'January', 800),
(2, 'February', 900),
(2, 'March', 1100);
Using the PIVOT Operator:
SELECT ProductID, [January], [February], [March]
FROM
(
SELECT ProductID, Month, SalesAmount
FROM SalesData
) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR Month IN ([January], [February], [March])
) AS PivotTable;
Explanation:
- The above query first selects the ProductID, Month, and SalesAmount from the SalesData table.
- Then, we use the PIVOT operator to group sales by month, which allows us to turn months like January, February, and March into separate columns.
- The final result shows the total sales for each product in each month.
Pivot Output:
|
ProductID |
January |
February |
March |
| 1 | 1000 | 1200 | 1500 |
| 2 | 800 | 900 | 1100 |
What is UNPIVOT in SQL?
The UNPIVOT operator works exactly the opposite of PIVOT. It takes the data that is spread across multiple columns and turns it back into rows. It can be helpful when you want to reorganize your data into the original form after the analysis, as it can be more reliable to store in a database.
UNPIVOT Syntax in SQL:
SELECT <columns>
FROM
(
SELECT <column1>, <column_to_unpivot1>, <column_to_unpivot2>, ...
FROM <table_name>
) AS SourceTable
UNPIVOT
(
<unpivot_column> FOR <new_column_name> IN (<list_of_columns_to_unpivot>)
) AS UnpivotTable;
The first step sets the table data as a new variable, SourceTable, which will then be used.
Next, the UNPIVOT operator is set to take certain columns and turn them into rows while also placing their values into a new column. The table is transformed from ‘SourceTable’ to ‘UnpivotTable’. The new table structure is now in row format instead of columns.
UNPIVOT Example
We’ve already rearranged the `SalesData` table by turning months into columns. Now, we want to reverse that process and return the data to its original form, where each month’s sales appear as rows again for each product.
Using the Unpivot Operator:
SELECT ProductID, Month, SalesAmount
FROM
(
SELECT ProductID, [January], [February], [March]
FROM
(
SELECT ProductID, Month, SalesAmount
FROM SalesData
) AS SourceTable
PIVOT
(
SUM(SalesAmount)
FOR Month IN ([January], [February], [March])
) AS PivotTable
) AS PivotedData
UNPIVOT
(
SalesAmount FOR Month IN ([January], [February], [March])
) AS UnpivotedData;
Explanation:
Here, we have first implemented the PIVOT operation in our first example. It aggregates and summarizes the data by month. Now, we have applied the UNPIVOT operator to change the months into rows back again. It will return my original table.
Unpivot Output:
|
ProductID |
Month |
Sales Amount |
| 1 | January | 1000 |
| 1 | February | 1200 |
| 1 | March | 1500 |
| 2 | January | 800 |
| 2 | February | 900 |
| 2 | March | 1100 |
When to Use Pivot and Unpivot in SQL?
Use Pivot when:
- You need to transform row data into columns for easier reporting or analysis.
- You are working with aggregate functions like SUM(), AVG(), COUNT(), and want to summarize data.
Use Unpivot when:
- You need to normalize data or restructure columns back into rows for analysis.
- Your data contains aggregated columns, and you need to break them down into individual rows.
Advanced Pivot and Unpivot Examples
Example 3: Pivot with Multiple Aggregations
Let’s say you want to show the total sales for each product every month.
SELECT ProductID, January, February, March,
January, February, March
FROM
(
SELECT ProductID, Month, SalesAmount
FROM SalesData
) AS SourceTable
PIVOT
(
SUM(SalesAmount) FOR Month IN ([January], [February], [March])
) AS PivotTable1
Explanation:
- The above query uses two PIVOT operators. One for total sales (SUM()) and another for average sales (AVG()).
Example 4: Unpivot with Multiple Columns
Let’s suppose you have a table showing quarterly sales data, and now you want to change it so that you will be able to show data for each month instead.
SELECT ProductID,
SUM(CASE WHEN Month IN ('January', 'February', 'March') THEN SalesAmount ELSE 0 END) AS Q1,
SUM(CASE WHEN Month IN ('April', 'May', 'June') THEN SalesAmount ELSE 0 END) AS Q2,
SUM(CASE WHEN Month IN ('July', 'August', 'September') THEN SalesAmount ELSE 0 END) AS Q3,
SUM(CASE WHEN Month IN ('October', 'November', 'December') THEN SalesAmount ELSE 0 END) AS Q4
INTO SalesDataQuarterly
FROM SalesData
GROUP BY ProductID;
SELECT ProductID, Quarter, SalesAmount
FROM SalesDataQuarterly
UNPIVOT
(
SalesAmount FOR Quarter IN (Q1, Q2, Q3, Q4)
) AS UnpivotedData;
Explanation:
- The above query uses the UNPIVOT operator to convert columns representing each quarter into rows.
Best Practices for Using Pivot and Unpivot in SQL
When you are using PIVOT or UNPIVOT, there are a few things that you need to keep in mind.
- Performance Considerations: When managing big data, pivoting and unpivoting are best done with caution, as they can adversely affect system performance. Use indexed views or temporary tables to enhance system performance.
- Readability: Large unpivot or pivot queries that use aggregations should be expressed in more straightforward subqueries to enhance understanding.
- Error Handling: When utilizing a dynamic approach to column names in PIVOT and UNPIVOT, take note of the possible errors that might stem from these operations.
- Data Types: It is essential that the data types of columns you are pivoting or unpivoting are compatible with each other for seamless execution.
Real-World Use Cases of PIVOT and UNPIVOT
- Reporting & Dashboards: Many businesses find it helpful to have sales reports that show monthly sales separately, making it easier to track performance and understand trends over time columns. To help you learn the concepts more quickly and make comparisons simpler, PIVOT plays a key role in presenting information in a clear and organized manner.
- ETL & Data Warehousing: The big tables in data warehouses, with many columns, can sometimes be a bit challenging to navigate. To make storage, analysis and integration into analytics systems easier, unpivoting helps to normalize data effectively.
- Comparative Analysis: Pivoting makes it super easy to organize and compare sales data from different years. This is really important for analysts because it helps them understand patterns, growth and seasonal changes with ease.
Conclusion
In SQL Server, the PIVOT and UNPIVOT operators assist you in transforming data from rows into columns and vice versa. These operations simplify reporting, data analysis, and summarizing data. By mastering these operators, you would be able to manage data transformations, enhancing your effectiveness in SQL effortlessly. Work with the examples given and get ready to use these operators confidently in your projects.