Reversing Data Pivoting with SQL UNPIVOT Operator

Unpivot Data Using UNPIVOT Operator

Write a SQL query to unpivot sales data from columns back into rows.

Solution:

-- Unpivot sales data from columns to rows.
SELECT Region, Year, SalesAmount
FROM SalesPivotData
UNPIVOT (
    SalesAmount FOR Year IN ([2021], [2022], [2023])
) AS UnpivotTable;

Explanation:

1. Purpose of the Query :

1. The goal is to transform sales data from columns (years) back into rows.

2. Key Components :

1. UNPIVOT Operator : Transforms columns into rows.
2. SalesAmount FOR Year IN : Specifies the columns to unpivot.

3. Why use Unpivoting? :

1. Unpivoting reverses the effect of pivoting, making data easier to analyze or process in its original form.

4. Real-World Application :

1. For example, in data analysis, you might use this query to normalize pivoted data for further processing.

Additional Notes:

• Unpivoting is useful when working with normalized data models.
• Use this exercise to teach how to reverse transformations for flexibility.

For more Practice: Solve these Related Problems:

• Write a SQL query to unpivot yearly revenue data from columns back into rows for further processing.
• Write a SQL query to transform pivoted survey responses back into a normalized row-based format.
• Write a SQL query to unpivot quarterly expense data into a single column for aggregation.
• Write a SQL query to reverse the effect of pivoting on a dataset containing product prices by store.

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

Previous SQL Exercise: Pivot Data Using PIVOT Operator.
Next SQL Exercise: Query JSON Data.