Pivot Flip

Imagine we’re working on a report for a manager who wants to see how well each product category is performing across different months.

But instead of reading through hundreds of rows, they want to see the months as columns, January, February, March, and so on, with sales totals neatly aligned under each. This layout makes it much easier to compare performance at a glance.

That’s where today’s concept, Pivot flip, comes into play.

In this lesson, we’ll explore how to transform the structure of a dataset by flipping rows into columns or vice versa. This transformation is especially useful when we need to reshape our data to support reports, dashboards, or analytical needs.

By the end of this lesson, we’ll be able to:

• Understand what a Pivot Flip is and why it’s valuable.

• Identify when to use it to reformat datasets.

• Write queries that transform rows into columns or columns into rows.

• Solve real-world problems using Pivot Flip patterns.

Let’s dive into one of the most powerful transformation techniques in SQL.

Pattern overview

Category:

• Transformation Patterns

Intent:

Reshape data by transposing rows into columns or columns into rows to make the dataset more suitable for analysis or reporting.

Motivation:

Raw data isn’t always in the shape we need.

Analysts and business stakeholders often prefer to compare categories, dates, or groups across columns for readability. The Pivot Flip pattern allows us to perform this transformation with SQL, making our results clearer and more impactful.

Also known as:

• Pivot

• Unpivot

• Crosstab

• Transpose

Structure

There are two key variants:

1. Pivot (Rows to columns): Aggregating data and displaying each category as a column.

2. Unpivot (Columns to rows): Converting wide tables into long format by flattening column-based values into rows.

We can implement these manually using CASE WHEN, or use built-in support like PIVOT (in SQL Server, Oracle) or UNPIVOT. However, in standard SQL (and MySQL), we typically use CASE expressions combined with aggregation.

Keywords

• CASE WHEN

• GROUP BY

• SUM(), COUNT(), MAX() (any aggregate)

• UNION ALL (for unpivot)

• CROSS JOIN (for filling pivots dynamically)

Problem structure

We use the Pivot Flip pattern when:

• We need to reshape data by turning rows into columns.

• We identify the grouping key and the attribute to pivot (e.g., category and month).

• We use CASE WHEN with optional aggregation (SUM, COUNT, etc.) to create new columns.

• Look for keywords like: “pivot,” “unpivot,” “reshape,” “spread across columns,” or “cross-tab” to identify when this pattern applies.

Example use case

1. Show monthly order counts per customer (pivoted view).

Given the following structure of the Orders table:

Where OrderID is the primary key and CustomerID is the foreign key from the Customers table referring to the customer who placed the order. The table contains information about the orders placed.

Write an SQL query to display the number of orders ...