PIVOT in SQL: How to Turn Rows into Columns

Say the database stores product sales by quarter. A standard GROUP BY returns this:

But for a report, we want quarters as columns:

This conversion from rows to columns is called PIVOT. In this article we'll explore how to do it in SQL using a delivery service database as an example.

The Problem: "Long" Format

This database has an events table with user actions across different platforms. Let's count events by type and platform:

MySQL 8.1
SELECT
    event_name,
    platform,
    COUNT(*) AS cnt
FROM events
GROUP BY event_name, platform
ORDER BY event_name, platform;

This is the "long" format. Each combination of "event + platform" occupies a separate row. To compare iOS with Android for a single event, you have to scan through the rows manually.

What we actually want is this:

Platforms became columns, and each event type is a single row. This is exactly the result we want.

How to Build a PIVOT: The Algorithm

Before writing the query, it helps to answer four questions:

1. What becomes the rows? event_name (event type)
2. What becomes the columns? platform (iOS, Android, Web)
3. What is the value in each cell? COUNT(*) (number of events)

CASE + GROUP BY: The Universal Approach

This approach works in any database: MySQL, PostgreSQL, SQLite, SQL Server.

MySQL 8.1
SELECT
    event_name,
    SUM(CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END) AS iOS,
    SUM(CASE WHEN platform = 'Android' THEN 1 ELSE 0 END) AS Android,
    SUM(CASE WHEN platform = 'Web' THEN 1 ELSE 0 END) AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

But how does this actually work? Let's break it down step by step.

What Happens Inside

To understand the mechanics, let's remove SUM and GROUP BY from the query and see what CASE computes for each individual row:

MySQL 8.1
SELECT
    event_name,
    platform,
    CASE WHEN platform = 'iOS' THEN 1 ELSE 0 END AS "iOS",
    CASE WHEN platform = 'Android' THEN 1 ELSE 0 END AS "Android",
    CASE WHEN platform = 'Web' THEN 1 ELSE 0 END AS "Web"
FROM events
WHERE event_name IN ('app_open', 'purchase')
LIMIT 6;

Each CASE checks the platform and assigns 1 for a match, 0 otherwise. Here's a sample of the result for clarity:

This sample shows rows from two groups: app_open and purchase. Each row gets 1 in exactly one column, and 0 in the rest. For example, a row with platform = 'iOS' gets 1 only in iOS.

Then GROUP BY groups rows by event_name, and SUM adds up the values within each group. The number of ones in the iOS column equals the number of events from iOS.

Note the ELSE 0 in CASE: without it, non-matching rows get NULL instead of 0, and the SUM result may also turn out NULL. More on this in the "NULL Trap" section below.

Why SUM and not COUNT? SUM is more convenient: just replace 1 with amount to calculate totals instead of counts. That's exactly what we'll do in the next example.

MySQL 8.1
SELECT
    event_name,
    COUNT(*) FILTER (WHERE platform = 'iOS') AS iOS,
    COUNT(*) FILTER (WHERE platform = 'Android') AS Android,
    COUNT(*) FILTER (WHERE platform = 'Web') AS Web
FROM events
GROUP BY event_name
ORDER BY event_name;

From Counts to Totals

So far we've been counting events. What if you need monetary totals instead? Just replace 1 with a real column, for example total_amount. Let's take another table from the same database, orders, and build a revenue report by order status for each month:

MySQL 8.1
SELECT
    DATE_FORMAT(order_date, '%Y-%m') AS month,
    SUM(CASE WHEN status = 'delivered' THEN total_amount ELSE 0 END) AS delivered,
    SUM(CASE WHEN status = 'canceled' THEN total_amount ELSE 0 END) AS canceled,
    SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END) AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

MySQL 8.1
SELECT
    to_char(order_date, 'YYYY-MM') AS month,
    SUM(total_amount) FILTER (WHERE status = 'delivered') AS delivered,
    SUM(total_amount) FILTER (WHERE status = 'canceled') AS canceled,
    SUM(total_amount) FILTER (WHERE status = 'refunded') AS refunded
FROM orders
WHERE order_date >= '2024-01-01' AND order_date < '2024-07-01'
GROUP BY month
ORDER BY month;

Things to Watch Out For

The NULL Trap

In the examples above we used ELSE 0 in CASE to guarantee a numeric result. But if you write CASE without ELSE:

MySQL 8.1
SUM(CASE WHEN status = 'refunded' THEN total_amount END)

...and some month has zero refunds, CASE returns NULL for every row, and SUM of all NULLs produces NULL. This is critical if you do further calculations: 100 - NULL = NULL.

There are two ways to avoid this:

1. Add ELSE 0 directly in CASE (as we did in the examples above):

   MySQL 8.1
   SUM(CASE WHEN status = 'refunded' THEN total_amount ELSE 0 END)
   

2. Wrap in COALESCE. Useful when the column may already contain NULL:

   MySQL 8.1
   COALESCE(SUM(CASE WHEN status = 'refunded' THEN total_amount END), 0)
   

You Must Know Column Values in Advance

All approaches require explicitly listing the values that become columns ('iOS', 'Android', 'Web'). If a new platform appears in the data, the query needs manual updating.

The only way around this: write a script on the application side (Python, PHP, JS) or use dynamic SQL (building the query string via PREPARE / stored procedures). Plain static SQL cannot solve this problem.

In most real-world tasks, the set of categories is known and changes rarely, so hardcoding values is perfectly normal.

MySQL 8.1
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
    $$
    SELECT event_name, platform, COUNT(*)
    FROM events
    GROUP BY event_name, platform
    ORDER BY event_name, platform
    $$,
    $$ VALUES ('Android'), ('iOS'), ('Web') $$
) AS ct(event_name TEXT, "Android" BIGINT, "iOS" BIGINT, "Web" BIGINT);

Summing Up