# SQL Pivots: Turning Rows Into Answers

Transactional data is rarely stored in the shape we want to analyse it in. A booking table holds one row per passenger:

Booking	Passenger	Gender	Seat Class	Fare
BK0001	Alice Mwangi	Female	Economy	1200
BK0002	Brian Otieno	Male	Business	2500
BK0003	Carol Wanjiku	Female	Economy	1200
BK0004	David Kamau	Male	Economy	1200

That structure is ideal for recording individual transactions, but consider the question it does not answer directly:

Do male and female passengers have different preferences for Economy and Business seats?

A standard GROUP BY returns a long-format result:

Gender	Seat Class	Bookings
Female	Economy	10
Female	Business	4
Male	Economy	8
Male	Business	7

The comparison becomes easier when seat_class is transformed into columns:

Gender	Economy Bookings	Business Bookings
Female	10	4
Male	8	7

This transformation is what we call a pivot.

What a pivot is

A pivot takes values stored as rows and turns them into columns. Instead of:

Female | Economy
Female | Business
Male   | Economy
Male   | Business

we produce:

Gender | Economy | Business
Female |   10    |    4
Male   |    8    |    7

The purpose is not to make a query look different. The purpose is to make comparisons easier to read.

The problem

The transport booking dataset contains a cleaned view, v_clean_trips, which includes the fields passenger_gender, seat_class and total_fare. The question we want to answer is:

How many Economy and Business bookings are made by each gender?

The first instinct is a straightforward aggregation:

SELECT
    passenger_gender,
    seat_class,
    COUNT(*) AS bookings
FROM v_clean_trips
GROUP BY passenger_gender, seat_class
ORDER BY passenger_gender, seat_class;

This is correct, but the result remains in long format:

Female | Economy  | 25
Female | Business | 8
Male   | Economy  | 21
Male   | Business | 12

For reporting purposes, a wide format is preferable:

Female | 25 | 8
Male   | 21 | 12

Building a pivot with CASE WHEN

CASE WHEN is one of the most useful ways to construct a pivot in SQL. The idea is simple. For Economy:

CASE
    WHEN seat_class = 'Economy' THEN 1
END

For Business:

CASE
    WHEN seat_class = 'Business' THEN 1
END

These expressions are then combined with an aggregate function such as COUNT():

SELECT
    passenger_gender,

    COUNT(
        CASE
            WHEN seat_class = 'Economy' THEN 1
        END
    ) AS economy_bookings,

    COUNT(
        CASE
            WHEN seat_class = 'Business' THEN 1
        END
    ) AS business_bookings

FROM v_clean_trips
GROUP BY passenger_gender
ORDER BY passenger_gender;

That is a complete SQL pivot.

What CASE WHEN is actually doing

Understanding the mechanism matters more than memorising the query. Consider:

CASE
    WHEN seat_class = 'Economy' THEN 1
END

SQL evaluates this against every row. An Economy seat returns 1; a Business seat matches no condition and returns NULL, because the expression has no ELSE branch. Internally, SQL is producing something equivalent to:

Seat Class | Economy CASE
-----------|-------------
Economy    | 1
Business   | NULL
Economy    | 1
Business   | NULL
Economy    | 1

COUNT() ignores nulls and counts only the non-null values. Therefore:

COUNT(
    CASE
        WHEN seat_class = 'Economy' THEN 1
    END
)

means count the rows where seat_class is Economy, and the Business equivalent counts the Business rows.

The reusable pattern

Once the mechanism is clear, the pattern generalises:

COUNT(
    CASE
        WHEN category = 'A' THEN 1
    END
)

Any distinct value of a column can be turned into its own column. Only the condition changes.

Adding revenue to the pivot

Counting bookings tells only half the business story. A second question follows naturally:

How much revenue came from Economy versus Business?

Here SUM() and CASE WHEN work together. For Economy revenue:

SUM(
    CASE
        WHEN seat_class = 'Economy'
        THEN total_fare
        ELSE 0
    END
)

The complete analysis combines both measures:

SELECT
    passenger_gender,

    COUNT(
        CASE
            WHEN seat_class = 'Economy' THEN 1
        END
    ) AS economy_bookings,

    SUM(
        CASE
            WHEN seat_class = 'Economy'
            THEN total_fare
            ELSE 0
        END
    ) AS economy_revenue,

    COUNT(
        CASE
            WHEN seat_class = 'Business' THEN 1
        END
    ) AS business_bookings,

    SUM(
        CASE
            WHEN seat_class = 'Business'
            THEN total_fare
            ELSE 0
        END
    ) AS business_revenue

FROM v_clean_trips
GROUP BY passenger_gender
ORDER BY passenger_gender;

The output now carries both volume and value:

Gender	Economy Bookings	Economy Revenue	Business Bookings	Business Revenue
Female	25	30,000	8	20,000
Male	21	25,200	12	30,000

Why GROUP BY is still required

This clause determines the rows:

GROUP BY passenger_gender

It states that the result should contain one row per passenger gender — one for Female, one for Male. The CASE WHEN expressions determine the columns, and the aggregate functions determine the values. The mental model is worth keeping:

GROUP BY    → controls the rows
CASE WHEN   → creates the pivot columns
COUNT / SUM → calculates the values

Pivoting is not magic

A pivot expression looks dense at first, but it is three operations running together:

1. Identify the group — GROUP BY passenger_gender
2. Identify the category — CASE WHEN seat_class = 'Economy'
3. Aggregate — COUNT(...)

Put together, they answer a single question: count Economy bookings for each gender. The same logic repeats for Business.

The technique is flexible

Nothing about the pattern is specific to seat classes. Transport types:

COUNT(
    CASE
        WHEN vehicle_type = 'Bus' THEN 1
    END
) AS bus_bookings

Routes:

SUM(
    CASE
        WHEN route_code = 'RT001'
        THEN total_fare
        ELSE 0
    END
) AS rt001_revenue

Payment methods:

COUNT(
    CASE
        WHEN payment_method = 'M-Pesa' THEN 1
    END
) AS mpesa_bookings

A pivot answers business questions

The more productive question is not how do I write a pivot but what comparison am I trying to make. Common analytical pairings include:

Analysis area	Comparison
Passenger behaviour	Gender × Seat Class
Revenue performance	Month × Route
Sales performance	Region × Product Category
Customer behaviour	Customer Segment × Payment Method
Healthcare analytics	Facility × Diagnosis

The database stores these dimensions vertically. The analysis is often easier when one of them becomes a set of columns.

Worked example: monthly route revenue

The same technique applies to route analysis. To compare revenue from the top routes by month:

SELECT
    TO_CHAR(departure_date, 'YYYY-MM') AS month,

    SUM(
        CASE
            WHEN route_code = 'RT001'
            THEN total_fare
            ELSE 0
        END
    ) AS rt001_revenue,

    SUM(
        CASE
            WHEN route_code = 'RT004'
            THEN total_fare
            ELSE 0
        END
    ) AS rt004_revenue,

    SUM(
        CASE
            WHEN route_code = 'RT002'
            THEN total_fare
            ELSE 0
        END
    ) AS rt002_revenue

FROM v_clean_trips
GROUP BY TO_CHAR(departure_date, 'YYYY-MM')
ORDER BY month;

The result:

Month	RT001	RT004	RT002
2026-01	125,000	89,000	76,000
2026-02	140,000	92,000	81,000
2026-03	155,000	97,000	88,000

No new technique was introduced. The only change was replacing seat_class with route_code.

Pivots and GROUP BY are not alternatives

They work together. A standard aggregation returns:

Gender | Seat Class | Bookings
Female | Economy    | 25
Female | Business   | 8
Male   | Economy    | 21
Male   | Business   | 12

A pivot returns:

Gender | Economy | Business
Female | 25      | 8
Male   | 21      | 12

The difference is representation. The long format suits detailed analysis; the wide format suits comparison, reporting and dashboards.

Dedicated PIVOT and UNPIVOT operators

Some database engines provide an explicit PIVOT operator that expresses the same idea as a named clause rather than a set of conditional aggregates. The general shape is:

SELECT column_list
FROM source_table
PIVOT (
    aggregate_function(measure_column)
    FOR pivot_column IN (list_of_values)
) AS alias;

Applied to the booking data, the seat-class pivot would read:

SELECT passenger_gender, Economy, Business
FROM (
    SELECT passenger_gender, seat_class, total_fare
    FROM v_clean_trips
) AS source_table
PIVOT (
    SUM(total_fare)
    FOR seat_class IN (Economy, Business)
) AS pivot_table;

Two points are worth noting before reaching for this syntax.

First, it is not portable. PIVOT and UNPIVOT are vendor extensions, available in SQL Server and Oracle but not in PostgreSQL, MySQL or SQLite. PostgreSQL offers crosstab() through the tablefunc extension as its nearest equivalent, but the conditional-aggregation approach shown earlier works everywhere and is the safer default for this project.

Second, it removes no real limitation. The values in the IN list still have to be written out by hand, exactly as the CASE WHEN conditions do. Neither approach discovers the categories for you; a genuinely dynamic column list requires dynamic SQL in both cases.

Unpivoting: turning columns back into rows

UNPIVOT is the inverse operation. It takes a wide table and returns it to long format — one row per value — which is often what a reporting tool or a normalised staging table actually wants:

SELECT column_list
FROM table_name
UNPIVOT (
    value_column
    FOR category_column IN (column_list)
) AS alias;

In PostgreSQL the same result is produced with a lateral VALUES list:

SELECT month, route_code, revenue
FROM route_revenue_wide
CROSS JOIN LATERAL (
    VALUES
        ('RT001', rt001_revenue),
        ('RT002', rt002_revenue),
        ('RT004', rt004_revenue)
) AS unpivoted(route_code, revenue);

UNION ALL achieves the same thing more verbosely, scanning the source once per column:

SELECT month, 'RT001' AS route_code, rt001_revenue AS revenue FROM route_revenue_wide
UNION ALL
SELECT month, 'RT002', rt002_revenue FROM route_revenue_wide
UNION ALL
SELECT month, 'RT004', rt004_revenue FROM route_revenue_wide;

One caveat about round-tripping. Unpivoting a pivoted result does not recover the original rows. The pivot applied SUM(), so several source rows were collapsed into one value; unpivoting restores the shape of the long format but returns the aggregated totals, not the individual bookings that produced them. The operation is reversible in layout only, not in detail.

Common mistakes

A trailing comma before FROM

COUNT(
    CASE
        WHEN seat_class = 'Business' THEN 1
    END
) AS business_bookings,

FROM v_clean_trips

The final comma is invalid. SQL expects another expression after a comma and instead encounters FROM. The last item in the SELECT list takes no comma:

COUNT(
    CASE
        WHEN seat_class = 'Business' THEN 1
    END
) AS business_bookings

FROM v_clean_trips

Confusing COUNT and SUM

These expressions answer different questions. For the number of bookings:

COUNT(
    CASE
        WHEN seat_class = 'Economy' THEN 1
    END
)

For revenue:

SUM(
    CASE
        WHEN seat_class = 'Economy'
        THEN total_fare
        ELSE 0
    END
)

A short reference:

Function	Question
COUNT	How many?
SUM	How much?
AVG	What is the average?
MAX	What is the maximum?
MIN	What is the minimum?

The CASE WHEN defines which records contribute to the calculation. The aggregate function defines what is calculated about them.

The bigger lesson

A pivot is not a formatting trick. It reshapes data so that a particular question becomes easier to answer. Raw data is optimised for transactions — one booking, one passenger, one seat class, one fare — while analytical questions are comparative: Female against Male, Economy against Business, Route A against Route B, January against February.

CASE WHEN matters because it does more than express conditional logic. It uses that logic to turn categories into analytical dimensions.

The pattern worth remembering

What should my rows be?
        ↓
GROUP BY

What should my columns be?
        ↓
CASE WHEN

What should I calculate?
        ↓
COUNT / SUM / AVG / etc.

For the passenger analysis:

Rows     → passenger_gender
Columns  → Economy / Business
Measures → bookings / revenue

Which translates directly into:

SELECT
    passenger_gender,

    COUNT(
        CASE WHEN seat_class = 'Economy' THEN 1 END
    ) AS economy_bookings,

    COUNT(
        CASE WHEN seat_class = 'Business' THEN 1 END
    ) AS business_bookings

FROM v_clean_trips
GROUP BY passenger_gender;

Once the pattern is familiar, pivot problems stop looking like complicated SQL and start looking like what they are: conditional aggregation used to reshape data for analysis.

Comments

[Raknaos]

The "what should my rows be / what should my columns be" framing is the part most people skip, and it is the one that saves the rewriting. One trap I would add to the measure row: COUNT(CASE WHEN seat_class = 'Economy' THEN 1 END) and SUM(CASE WHEN seat_class = 'Economy' THEN 1 ELSE 0 END) give the same number only because of the ELSE 0. Drop it and SUM returns NULL for a group with no matches, which then silently poisons anything you do with the column afterwards — totals, ratios, ordering. I now write the zero explicitly even when COUNT would have been fine.

The other thing that bit me on pivots like this: if the seat class can arrive with inconsistent spelling or trailing space from an import, you get a quiet extra column of NULLs instead of an error. A quick SELECT DISTINCT seat_class FROM v_clean_trips before trusting the shape of the result is the cheapest two minutes in this whole workflow.