Pivoting and Unpivoting

Introduction

Pivoting and unpivoting are essential SQL operations that transform data structures for better insights and flexibility. These techniques are widely used in data analysis, reporting, and ETL (Extract, Transform, Load) processes. In this guide, we’ll provide examples of pivoting and unpivoting in SQL, optimized for readers interested in data transformation and database management.

What is Pivoting in SQL?

Pivoting converts rows into columns to display aggregated data in a more readable format. This is particularly useful for creating summary reports.

Example: Pivoting Sales Data

Scenario: You have a Sales table containing product sales by year.

Goal: Transform the rows of years into columns.

Desired Output:

SQL Query Using Conditional Aggregation:

SELECT
    Product,
    SUM(CASE WHEN Year = 2023 THEN Sales ELSE 0 END) AS "2023 Sales",
    SUM(CASE WHEN Year = 2024 THEN Sales ELSE 0 END) AS "2024 Sales"
FROM Sales
GROUP BY Product;

Benefits of Pivoting:

• Makes data easier to read for reporting.
• Aggregates information for specific categories.

What is Unpivoting in SQL?

Unpivoting transforms columns into rows, making the data more normalized. This is useful for preparing data for analysis tools and database operations.

Example: Unpivoting Sales Data

Scenario: You have a table summarizing sales data by product and year.

Goal: Convert the columns for each year into rows.

Desired Output:

SQL Query Using UNION ALL:

SELECT '2023' AS Year, Product, [2023 Sales] AS Sales
FROM SalesData
UNION ALL
SELECT '2024' AS Year, Product, [2024 Sales] AS Sales
FROM SalesData;

SQL Query Using UNPIVOT (SQL Server):

SELECT
    Year,
    Product,
    Sales
FROM (
    SELECT Product, [2023 Sales], [2024 Sales]
    FROM SalesData
) src
UNPIVOT (
    Sales FOR Year IN ([2023 Sales], [2024 Sales])
) unpvt;

Benefits of Unpivoting:

• Simplifies data for analysis or integration with tools like Tableau or Power BI.
• Converts wide tables into a long format, better suited for processing.