Pivot-like Data Transposition

In this lesson, you will learn how to shift data from a vertical "tall" format into a horizontal "wide" format to make your reports easier to read.

Why Transpose?

Often, databases store information in an "Entity-Attribute-Value" style. For example, a Sales table might list each order on its own row, with a Product column and a Revenue column. If you want a report showing total revenue for "Apples," "Oranges," and "Bananas" side-by-side, a standard GROUP BY isn't enough because it would list them row by row. Transposition allows us to turn those individual category rows into distinct columns.

Using Conditional Aggregation

To achieve this, we use the "Conditional Aggregation" technique. Since you already know how to use CASE statements (from our lesson on Conditional Value Mapping) and SUM() (from our lesson on Aggregating Data), we can combine them to force specific data into their own "buckets."

Imagine we have a table of monthly sales and we want to see them displayed as columns:

SELECT 
    region,
    SUM(CASE WHEN product = 'Apple' THEN revenue ELSE 0 END) AS apple_sales,
    SUM(CASE WHEN product = 'Orange' THEN revenue ELSE 0 END) AS orange_sales,
    SUM(CASE WHEN product = 'Banana' THEN revenue ELSE 0 END) AS banana_sales
FROM Sales
GROUP BY region;

How it Works

1. The CASE logic: For every row, the SQL engine checks the product. If it matches our target, it "captures" the revenue; if not, it returns 0.
2. The SUM scope: Because we are grouping by region, the SUM function adds up only the captured values for that specific category within that region.
3. The Result: Instead of seeing one row per product, you get a clean, wide report where each product is a dedicated column. This is perfect for stakeholders who want to compare categories at a glance without performing extra mental math or manual spreadsheet work.

This pattern is the foundation for creating professional, summary-style dashboards directly within your query results.

Key Takeaway: You can transform vertical data into horizontal layouts by using the SUM(CASE WHEN ...) pattern to isolate specific categories into their own columns during the aggregation phase.