Using SQL PIVOT and UNPIVOT for Data Transformation

Combine PIVOT and UNPIVOT Operators

Write a SQL query to pivot sales data and then unpivot it back to its original form.

Solution:

-- Pivot and unpivot sales data.
WITH PivotedData AS (
    SELECT *
    FROM (
        SELECT Region, Year, SalesAmount
        FROM SalesData
    ) AS SourceTable
    PIVOT (
        SUM(SalesAmount)
        FOR Year IN ([2021], [2022], [2023])
    ) AS PivotTable
),
UnpivotedData AS (
    SELECT Region, Year, SalesAmount
    FROM PivotedData
    UNPIVOT (
        SalesAmount FOR Year IN ([2021], [2022], [2023])
    ) AS UnpivotTable
)
SELECT * FROM UnpivotedData;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to pivot sales data into columns and then unpivot it back into rows.

2. Key Components :

1. PIVOT Operator : Transforms rows into columns.
2. UNPIVOT Operator : Transforms columns back into rows.
3. WITH Clause : Combines both operations using CTEs.

3. Why Combine PIVOT and UNPIVOT? :

1. This demonstrates the reversibility of transformations, which is useful for testing and data normalization.

4. Real-World Application :

1. For example, in ETL processes, you might use this query to test data transformations before loading into a target system.

Additional Notes:

• Ensure that the column names in PIVOT and UNPIVOT match exactly.
• Use this exercise to teach how to validate data transformations.

For more Practice: Solve these Related Problems:

• Write a SQL query to pivot inventory data by location and then unpivot it back to its original form.
• Write a SQL query to transform monthly sales data into quarterly columns and revert it to rows using UNPIVOT.
• Write a SQL query to pivot student grades by subject and then unpivot them for normalization.
• Write a SQL query to pivot employee attendance data by month and then unpivot it for further analysis.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous SQL Exercise: Handle XML Data with XPath Queries.
Next SQL Exercise: Query Nested JSON Arrays.