In this lesson, you will learn how to transform "wide" tables—where metrics are spread across multiple columns—into a "long" format that is easier to aggregate and query relationally.
Why Reshape?
In our previous lessons on CUBE, ROLLUP, and dynamic pivoting, we often dealt with data that was already perfectly organized. However, real-world data frequently arrives in a "wide" format. For example, imagine a sales table where each row represents a store, and individual columns represent sales for jan, feb, and mar:
| store_id | jan_sales | feb_sales | mar_sales |
|---|---|---|---|
| 101 | 500 | 700 | 600 |
While this looks readable, it is difficult to perform advanced operations like GROUP BY or windowing across the time dimension. To run a rolling average or a time-series analysis, we need to "melt" these columns into rows.
The Technique: UNION ALL
The most reliable way to normalize this structure in SQL is to use UNION ALL. By stacking the columns, we create a single month column and a single amount column:
SELECT store_id, 'jan' AS month, jan_sales AS amount FROM sales
UNION ALL
SELECT store_id, 'feb' AS month, feb_sales AS amount FROM sales
UNION ALL
SELECT store_id, 'mar' AS month, mar_sales AS amount FROM sales;
Resulting Long Format
The output of this query transforms the data into this structure:
| store_id | month | amount |
|---|---|---|
| 101 | jan | 500 |
| 101 | feb | 700 |
| 101 | mar | 600 |
Why This Matters
By shifting from wide to long, you unlock the ability to use the relational tools we have already mastered. You can now easily calculate the AVG(amount) per store, partition by the month column to detect anomalies, or use a window function to calculate cumulative totals across time periods—operations that are nearly impossible when metrics are trapped in isolated, horizontal columns.
Key Takeaway: Normalizing wide tables into a long format via UNION ALL allows you to treat time or categories as relational dimensions, enabling powerful, scalable aggregations.