Editorial Team · on 13 June 2026 · 6 min read · Last reviewed 13 June 2026

SQL and Relational Database Tutorials provide developers and data analysts with structured learning resources to master advanced SQL techniques, including pivot and unpivot operations.

Key facts

• Pivot operations transform rows into columns for cross-tabulation.
• Unpivot operations convert columns back into rows for normalization.
• SQL Server, Oracle, and PostgreSQL support PIVOT and UNPIVOT with dedicated syntax.
• Pivot and unpivot operations improve data presentation and analysis efficiency.

What are SQL Pivot Operations?

SQL PIVOT operations convert unique values from one column into multiple columns, effectively rotating data from a row orientation to a column orientation. This is particularly useful for creating crosstab reports or summarizing data in a more readable format. PIVOT operations are supported natively in SQL Server, Oracle, and PostgreSQL, though the syntax may vary slightly between these databases.

For example, consider a table containing sales data with columns for product, region, and sales amount. Using a PIVOT operation, you can transform this data to show products as rows and regions as columns, with the sales amounts filling the cells. This makes it easier to compare sales across regions for each product.

How to Implement PIVOT Operations in SQL?

To implement a PIVOT operation, you need to specify the column that contains the values to be rotated into columns, the column that provides the values for these new columns, and an aggregation function to handle multiple values. Here’s a basic example using SQL Server syntax:

SELECT Product, [North], [South], [East], [West]
FROM
(
    SELECT Product, Region, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Region IN ([North], [South], [East], [West])
) AS PivotTable;

This query pivots the sales data so that each region becomes a column, and the sales amounts are summed for each product in each region. The same logic can be applied in Oracle and PostgreSQL with slight syntax adjustments.

Database	PIVOT Syntax	Example
SQL Server	PIVOT (aggregation_function(column) FOR column IN (values))	SUM(SalesAmount) FOR Region IN ([North], [South])
Oracle	PIVOT (aggregation_function(column) FOR column IN (values))	SUM(SalesAmount) FOR Region IN (‘North’, ‘South’)
PostgreSQL	crosstab(source, category_list) with explicit function call	crosstab(‘SELECT Product, Region, SalesAmount FROM SalesData’, ‘SELECT DISTINCT Region FROM SalesData’)

What are SQL Unpivot Operations?

SQL UNPIVOT operations perform the inverse of PIVOT operations, converting columns back into rows. This is useful for normalizing data or preparing it for further analysis. UNPIVOT operations take multiple columns and transform them into a single column with repeated values, which can simplify complex queries and data transformations.

For instance, if you have a table with columns for product, North sales, South sales, East sales, and West sales, an UNPIVOT operation can transform this into a table with columns for product, region, and sales amount, where each row represents the sales amount for a product in a specific region.

How to Implement UNPIVOT Operations in SQL?

To implement an UNPIVOT operation, you need to specify the columns that contain the values to be rotated into rows, the column that will hold the new category values, and the column that will hold the values from the original columns. Here’s an example using SQL Server syntax:

SELECT Product, Region, SalesAmount
FROM
(
    SELECT Product, [North], [South], [East], [West]
    FROM SalesData
) AS SourceTable
UNPIVOT
(
    SalesAmount FOR Region IN ([North], [South], [East], [West])
) AS UnpivotTable;

This query unpivots the sales data so that each region’s sales amount is converted into a separate row for each product. The same logic can be applied in Oracle and PostgreSQL with slight syntax adjustments.

Database	UNPIVOT Syntax	Example
SQL Server	UNPIVOT (column FOR category IN (values))	SalesAmount FOR Region IN ([North], [South])
Oracle	UNPIVOT (column FOR category IN (values))	SalesAmount FOR Region IN (‘North’, ‘South’)
PostgreSQL	No native UNPIVOT syntax; use UNION ALL with CASE statements	SELECT Product, ‘North’ AS Region, [North] AS SalesAmount FROM SalesData UNION ALL SELECT Product, ‘South’ AS Region, [South] AS SalesAmount FROM SalesData

When to Use PIVOT and UNPIVOT Operations?

PIVOT and UNPIVOT operations are particularly useful in scenarios where you need to transform data for reporting or analysis purposes. PIVOT operations are ideal for creating crosstab reports, summarizing data, and making it more readable. UNPIVOT operations are useful for normalizing data, preparing it for further analysis, and simplifying complex queries.

For example, in a sales report, you might use a PIVOT operation to show products as rows and regions as columns, with the sales amounts filling the cells. This makes it easier to compare sales across regions for each product. Conversely, you might use an UNPIVOT operation to convert a table with multiple sales columns into a single column with repeated values, making it easier to analyze the data further.

Best Practices for PIVOT and UNPIVOT Operations

When using PIVOT and UNPIVOT operations, it’s important to follow best practices to ensure optimal performance and readability. Here are some key tips:

1. Use Descriptive Column Names: Ensure that the new columns created by PIVOT operations have descriptive names to make the data easier to understand.
2. Handle Null Values: Be aware of how null values are handled in PIVOT operations, as they can affect the results. Use appropriate aggregation functions to handle null values effectively.
3. Optimize Performance: PIVOT and UNPIVOT operations can be resource-intensive, especially with large datasets. Optimize your queries by filtering data before applying these operations.
4. Test with Sample Data: Always test your PIVOT and UNPIVOT operations with sample data to ensure they work as expected before applying them to your entire dataset.

Advanced Techniques for PIVOT and UNPIVOT Operations

In addition to the basic PIVOT and UNPIVOT operations, there are advanced techniques that can enhance your data transformations. For example, you can use dynamic SQL to create PIVOT operations with dynamic column lists, allowing you to handle varying numbers of columns or values. This is particularly useful when the number of columns or values is not known in advance.

Another advanced technique is combining PIVOT and UNPIVOT operations with other SQL features, such as window functions or common table expressions (CTEs). This can help you create more complex and powerful data transformations. For more details on window functions, see Understanding and Using SQL Window Functions. For information on CTEs, see Implementing Common Table Expressions (CTEs) in SQL.

Practical Examples of PIVOT and UNPIVOT Operations

To illustrate the practical applications of PIVOT and UNPIVOT operations, let’s consider a few examples. Suppose you have a table containing sales data for different products in various regions. You can use a PIVOT operation to transform this data into a crosstab format, making it easier to compare sales across regions for each product.

SELECT Product, [North], [South], [East], [West]
FROM
(
    SELECT Product, Region, SalesAmount
    FROM SalesData
) AS SourceTable
PIVOT
(
    SUM(SalesAmount)
    FOR Region IN ([North], [South], [East], [West])
) AS PivotTable;

Similarly, you can use an UNPIVOT operation to convert a table with multiple sales columns into a single column with repeated values, making it easier to analyze the data further.

SELECT Product, Region, SalesAmount
FROM
(
    SELECT Product, [North], [South], [East], [West]
    FROM SalesData
) AS SourceTable
UNPIVOT
(
    SalesAmount FOR Region IN ([North], [South], [East], [West])
) AS UnpivotTable;

Mastering PIVOT and UNPIVOT operations can significantly enhance your ability to transform and analyze data in SQL. By understanding the syntax and best practices for these operations, you can create more efficient and effective data transformations, improving your overall data analysis capabilities. For more advanced SQL techniques, see Mastering Advanced SQL Techniques for Developers and Data Analysts. To optimize your SQL queries for performance, see Optimizing SQL Queries for Performance. For more on advanced SQL joins, see Advanced SQL Joins: Beyond the Basics. To learn about stored procedures and functions, see Using Stored Procedures and Functions in SQL.

Frequently asked questions

What is the purpose of SQL Pivot operations?

SQL Pivot operations transform rows into columns, summarizing data for better readability. For example, you can pivot monthly sales data to display each month as a column, making it easier to compare sales across different months.

How do Unpivot operations differ from Pivot operations?

Unpivot operations reverse the process, converting columns into rows. This is useful for normalizing data. For instance, unpivoting a table with monthly sales columns into rows can simplify data analysis and integration.

What are the basic requirements for performing a Pivot operation?

To perform a Pivot operation, you need an aggregate function, a column to pivot, and a column to group by. For example, using SUM as the aggregate function, the month column to pivot, and the product category column to group by.

Can you provide an example of a simple Pivot operation in SQL?

Certainly. Consider a sales table with columns for product, month, and sales amount. A Pivot operation can transform this data to display products as rows and months as columns, with each cell containing the total sales for that product and month.