Pivot and Unpivot in SQL: Transform Your Data Easily

In SQL, Pivot converts rows into columns to summarize data, while Unpivot reverses the process—turning columns into rows for easier data normalization and flexible reporting.

Data reshaping is essential in SQL for creating meaningful insights from raw datasets. Whether you’re summarizing sales figures by region or converting complex tables for analytics, Pivot and Unpivot simplify reporting and data preparation. They help build cleaner dashboards, automate report generation, and improve overall data usability.

Key Takeaways of Pivot and Unpivot in SQL

Feature	Pivot	Unpivot
Purpose	Convert rows → columns	Convert columns → rows
Common Use	Summarized reports	Normalized data

Understanding Pivot in SQL

Syntax and Working Principle

The PIVOT operator in SQL is used to transform data from rows into columns, allowing you to summarize and display data in a more readable, cross-tabular format. It’s especially useful when you need to aggregate values for multiple categories.

Basic Syntax:

SELECT <non-pivoted column>, [first_column], [second_column], ...
FROM 
(
    SELECT <column_to_aggregate>, <column_to_group>, <column_to_pivot>
    FROM table_name
) AS source
PIVOT
(
    AGGREGATE_FUNCTION(<column_to_aggregate>)
    FOR <column_to_pivot> IN ([first_column], [second_column], ...)
) AS pivot_table;

Example with Table and Output

Sample Data (Sales Table):

Region	Month	Sales
East	Jan	5000
West	Jan	4000
East	Feb	7000
West	Feb	6000

Pivot Query:

SELECT *
FROM (
    SELECT Region, Month, Sales
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Sales)
    FOR Month IN ([Jan], [Feb])
) AS PivotTable;

Output:

Region	Jan	Feb
East	5000	7000
West	4000	6000

Practical Scenarios

Sales Reports: Compare monthly or quarterly sales per region or product.
Student Scores: Display subjects as columns to compare marks per student.

Inventory Management: Show stock levels across different stores or categories.

Understanding Unpivot in SQL

Syntax Explanation

The UNPIVOT operator performs the reverse of Pivot—it transforms columns into rows. It’s commonly used when data is stored in a cross-tab format but needs to be normalized for analysis or database operations.

Basic Syntax:

SELECT <non-unpivoted column>, <unpivoted_column_name>, <value_column_name>
FROM 
(
    SELECT <column_list>
    FROM table_name
) AS source
UNPIVOT
(
    <value_column_name> FOR <unpivoted_column_name> IN (<column_list>)
) AS unpivot_table;

Example with Code and Output

Sample Data (After Pivot):

Region	Jan	Feb
East	5000	7000
West	4000	6000

Unpivot Query:

SELECT Region, Month, Sales
FROM (
    SELECT Region, Jan, Feb
    FROM Sales_Pivot
) AS PivotedData
UNPIVOT
(
    Sales FOR Month IN (Jan, Feb)
) AS UnpivotedData;

Output:

Region	Month	Sales
East	Jan	5000
East	Feb	7000
West	Jan	4000
West	Feb	6000

When and Why to Use Unpivot

When you receive denormalized data from reports or Excel sheets.
To prepare data for statistical analysis, where rows represent individual observations.
When integrating Pivoted data back into ETL workflows or analytics dashboards.

Comparison: Pivot and Unpivot in SQL

Criteria	Pivot	Unpivot
Purpose	Converts rows into columns	Converts columns into rows
Use Case	Summary reports, dashboards	Data normalization, ETL
Function Type	Aggregation	Reversal of aggregation
Example Output	Month-wise sales per region	Region-month sales list
Result Structure	Wide table	Tall table

Pivot and Unpivot in SQL: Common Errors and How to Fix Them

Error Type	Cause	Fix
Data Type Mismatch	Columns used in Pivot/Unpivot have different data types.	Use `CAST()` or `CONVERT()` to standardize data types before transformation.
Alias Naming Issue	Missing or invalid alias for pivoted/unpivoted tables.	Always provide valid aliases after PIVOT or UNPIVOT blocks.
NULL Handling	Empty cells result in NULLs during pivoting.	Use `ISNULL()` or `COALESCE()` to replace NULLs with default values.

Real-Life Uses of Pivot and Unpivot in SQL

Sales Data Analysis at Amazon
Amazon uses SQL Pivot to transform sales data, turning dates into columns for a clearer year-on-year trend analysis. This aids in tracking product sales growth over time.
```
SELECT * FROM (
    SELECT product, year, sales
    FROM sales_data
) src
PIVOT (
    SUM(sales)
    FOR year IN ([2019], [2020], [2021])
) AS pvt
```
Output:
| product | 2019 | 2020 | 2021 |
|———|——|——|——|
| Book | 1000 | 1500 | 2000 |
Customer Reviews Aggregation at Netflix
Netflix uses SQL Unpivot to aggregate user ratings into a consistent format for sentiment analysis. This allows them to understand viewing preferences and improve recommendations.
```
SELECT movie, rating, score
FROM (
    SELECT movie, star1, star2, star3, star4, star5
    FROM movie_ratings
) src
UNPIVOT (
    score FOR rating IN (star1, star2, star3, star4, star5)
) AS unpvt
```
Output:
| movie | rating | score |
|——–|——–|——-|
| Film A | star1 | 100 |
Employee Performance Metrics at Google
Google utilises Pivot to convert rows of employee performance data into columns, making it easier for management to compare yearly employee performance and set targets.
```
SELECT * FROM (
    SELECT employee_id, year, performance_score
    FROM performance_metrics
) src
PIVOT (
    AVG(performance_score)
    FOR year IN ([2019], [2020], [2021])
) AS pvt 
```
Output:
| employee_id | 2019 | 2020 | 2021 |
|————-|——|——|——|
| 12345 | 85 | 90 | 88 |

Pivot and Unpivot in SQL Interview Focus

Why would you use PIVOT and UNPIVOT in SQL instead of doing it manually in Excel?
Using PIVOT and UNPIVOT in SQL allows for more dynamic and automated transformations of data, especially with large datasets. It’s much more efficient than manual Excel operations, reducing the chance of human error and allowing for updates without manually redrawing tables every time data changes.
Can PIVOT and UNPIVOT be used on non-numeric data types?
Yes, they can be used with non-numeric data types. However, it’s important to ensure that any aggregation functions applied are suited to the data type you’re working with.

How do you perform a PIVOT operation where multiple aggregates are required?
You can perform multiple aggregate functions by including them within the PIVOT syntax, separated by commas. A typical query might look like this:


        SELECT * FROM 
        (SELECT Year, Product, Sales FROM Products) 
        AS SourceTable
        PIVOT 
        (SUM(Sales) FOR Product IN ([Product1], [Product2])) 
        AS PivotTable;

How do UNPIVOT operators affect data with NULL values?
UNPIVOT operations can introduce additional rows or columns, especially dealing with NULL values. It’s essential to handle or filter out NULLs appropriately according to your needs to maintain data integrity.
What are some common mistakes beginners make with PIVOT and UNPIVOT?
Common mistakes include not using the correct alias names, misunderstanding the aggregation functions, and mismatching columns between the subquery and the PIVOT or UNPIVOT table.
How different is SQL PIVOT function from a CROSS TAB functionality?
SQL PIVOT offers a way to transform and rotate data similarly to cross tabs or data crosstabs, but with more flexibility and integration within SQL scripts for larger datasets, providing support for more complex queries and relationships.

Can you provide an example of a complex UNPIVOT query?
Here’s a simple example involving sales data:


        SELECT Product, Measure, Value 
        FROM 
        (SELECT Product, Sales, Returns FROM SalesData) 
        AS SourceTable
        UNPIVOT 
        (Value FOR Measure IN (Sales, Returns))
        AS UnpivotTable;

Do PIVOT and UNPIVOT operations always result in better query performance?
Not necessarily. Depending on the complexity and structure of your tables, these operations can sometimes lead to performance overhead, especially if not indexed properly.

Our AI-powered SQL online compiler transforms your coding experience, letting you write, run, and test your code instantly. It uses cutting-edge AI to streamline debugging and accelerate learning. Whether you’re a beginner or a seasoned pro, this tool makes SQL simpler and more accessible than you ever imagined.

Conclusion

Remember, by completing Pivot and Unpivot in SQL, you’ve not only sharpened essential skills but also unlocked a rewards system of personal achievement. Embrace the challenge, practise consistently, and soon you’ll navigate SQL like a pro. Click on Newtum to explore more about programming wonders, and get ready for an amazing coding journey!

Edited and Compiled by

This article was compiled and edited by @rasikadeshpande, who has over 4 years of experience in writing. She’s passionate about helping beginners understand technical topics in a more interactive way.

About The Author

Rasika Deshpande

Rasika Deshpande, a passionate writer with over 4 years of experience in the EdTech industry. At Newtum, she writes, edit, and compile engaging blog and other content. Writing is not just a profession for her; it’s a passion that fuels creativity and dedication to the field of writing.

See author's posts