SQL Pivot Table Guide

January 7, 2026 By Matthew Rathbone

A pivot table turns rows into columns: one row per group, and one column for each value of another field, such as a column per quarter. In SQL you build one with CASE inside an aggregate like SUM, grouped by whatever each row should be. The examples work in PostgreSQL, MySQL, SQLite and SQL Server.

How to make a pivot table in SQL

Write one aggregate per output column. The CASE inside it lets through only the rows that belong to that column, and GROUP BY gives one row per group:

SELECT
  row_column,
  SUM(CASE WHEN pivot_column = 'value 1' THEN amount ELSE 0 END) AS value_1,
  SUM(CASE WHEN pivot_column = 'value 2' THEN amount ELSE 0 END) AS value_2
FROM table_name
GROUP BY row_column;

Here each product gets one row, with its units for each quarter side by side:

-- Input: sales
-- product  | quarter | units
-- ---------+---------+------
-- Keyboard | Q1      | 120
-- Keyboard | Q2      | 95
-- Monitor  | Q1      | 40
-- Monitor  | Q2      | 55
-- Mouse    | Q2      | 210

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN units ELSE 0 END) AS q2
FROM sales
GROUP BY product
ORDER BY product;

-- Output:
-- product  | q1  | q2
-- ---------+-----+----
-- Keyboard | 120 | 95
-- Monitor  | 40  | 55
-- Mouse    | 0   | 210

Rules to know

You name every column yourself. SQL can’t create columns from the data, so a third quarter needs a third CASE line. If the list of values keeps changing, build the query in your application code.
ELSE 0 is for SUM and counts only. It turns a missing combination into 0 instead of NULL. With AVG, MIN or MAX, leave ELSE out, or the zeros count as real values:

-- Input: the sales table above

SELECT
  product,
  ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units ELSE 0 END), 1) AS q1_else_0,
  ROUND(AVG(CASE WHEN quarter = 'Q1' THEN units END), 1) AS q1_no_else
FROM sales
GROUP BY product
ORDER BY product;

-- Output:
-- product  | q1_else_0 | q1_no_else
-- ---------+-----------+-----------
-- Keyboard | 60.0      | 120.0
-- Monitor  | 20.0      | 40.0
-- Mouse    | 0.0       | NULL

Alternative syntax

You’ll also meet FILTER in PostgreSQL and SQLite, and the PIVOT operator in SQL Server. Both leave a missing combination as NULL rather than 0:

-- Input: the sales table above

-- PostgreSQL and SQLite
SELECT
  product,
  SUM(units) FILTER (WHERE quarter = 'Q1') AS q1,
  SUM(units) FILTER (WHERE quarter = 'Q2') AS q2
FROM sales
GROUP BY product
ORDER BY product;

-- SQL Server
SELECT product, [Q1] AS q1, [Q2] AS q2
FROM (SELECT product, quarter, units FROM sales) AS src
PIVOT (SUM(units) FOR quarter IN ([Q1], [Q2])) AS p
ORDER BY product;

-- Output:
-- product  | q1   | q2
-- ---------+------+----
-- Keyboard | 120  | 95
-- Monitor  | 40   | 55
-- Mouse    | NULL | 210

In MySQL code you’ll often see SUM(IF(quarter = 'Q1', units, 0)), a shorter way to write the CASE version. PostgreSQL’s tablefunc extension also has a crosstab() function; see creating pivot tables in PostgreSQL with crosstab().

When should you use a pivot table?

Pivot when a list of rows would be easier to read as a grid. These are the usual reasons.

Compare one measure side by side

Put the values you want to compare (days, quarters, regions) next to each other in one row, instead of scattered down a long list.

Show more than one measure for each column

Each output column is its own aggregate, so you can pair them up: sessions and pageviews per device, or orders and revenue per channel.

Count rows in each category

To count instead of add up, sum 1 for each matching row, or use COUNT(CASE WHEN ... THEN 1 END): COUNT skips the NULLs that the other rows produce. This is how you get tasks per status or tickets per priority.

Turn key-value rows into columns

Settings and product attributes are often stored as one row per name and value. Pivoting with MAX(CASE ...) gives one row per item with a column per attribute. MAX works on text and picks the single value in each group.

Real examples of SQL pivot tables

Average app rating by platform

Leave out ELSE when you average, so an app with no reviews on a platform shows NULL instead of a misleading score:

-- Input: reviews
-- app      | platform | rating
-- ---------+----------+-------
-- Budgetly | iOS      | 5
-- Budgetly | iOS      | 4
-- Budgetly | Android  | 3
-- FitTrack | Android  | 4
-- FitTrack | Android  | 5

-- In SQL Server, AVG of an integer column drops the decimals: use rating * 1.0
SELECT
  app,
  ROUND(AVG(CASE WHEN platform = 'iOS' THEN rating END), 1) AS ios,
  ROUND(AVG(CASE WHEN platform = 'Android' THEN rating END), 1) AS android
FROM reviews
GROUP BY app
ORDER BY app;

-- Output:
-- app      | ios  | android
-- ---------+------+--------
-- Budgetly | 4.5  | 3.0
-- FitTrack | NULL | 4.5

Orders and sales by channel for each region

Two measures for each channel, an order count and a total:

-- Input: orders
-- id | region | channel | amount
-- ---+--------+---------+-------
-- 1  | North  | web     | 120.00
-- 2  | North  | store   | 80.00
-- 3  | North  | web     | 45.50
-- 4  | South  | store   | 210.00
-- 5  | South  | store   | 64.00

SELECT
  region,
  COUNT(CASE WHEN channel = 'web' THEN 1 END) AS web_orders,
  SUM(CASE WHEN channel = 'web' THEN amount ELSE 0 END) AS web_sales,
  COUNT(CASE WHEN channel = 'store' THEN 1 END) AS store_orders,
  SUM(CASE WHEN channel = 'store' THEN amount ELSE 0 END) AS store_sales
FROM orders
GROUP BY region
ORDER BY region;

-- Output:
-- region | web_orders | web_sales | store_orders | store_sales
-- -------+------------+-----------+--------------+------------
-- North  | 2          | 165.50    | 1            | 80.00
-- South  | 0          | 0         | 2            | 274.00

Support tickets per agent by priority

A count for each priority, plus a total. COUNT(*) still counts every row in the group:

-- Input: tickets
-- id | agent | priority
-- ---+-------+---------
-- 1  | Priya | urgent
-- 2  | Priya | normal
-- 3  | Marco | normal
-- 4  | Priya | normal
-- 5  | Marco | low

SELECT
  agent,
  COUNT(CASE WHEN priority = 'low' THEN 1 END) AS low,
  COUNT(CASE WHEN priority = 'normal' THEN 1 END) AS normal,
  COUNT(CASE WHEN priority = 'urgent' THEN 1 END) AS urgent,
  COUNT(*) AS total
FROM tickets
GROUP BY agent
ORDER BY agent;

-- Output:
-- agent | low | normal | urgent | total
-- ------+-----+--------+--------+------
-- Marco | 1   | 1      | 0      | 2
-- Priya | 0   | 2      | 1      | 3

One row of settings per user

Each setting is stored as a name and a value. A user who never set one gets NULL in that column:

-- Input: user_settings
-- user_id | setting  | value
-- --------+----------+--------------
-- 1       | theme    | dark
-- 1       | language | en
-- 2       | language | de
-- 2       | timezone | Europe/Berlin
-- 3       | theme    | light

SELECT
  user_id,
  MAX(CASE WHEN setting = 'theme' THEN value END) AS theme,
  MAX(CASE WHEN setting = 'language' THEN value END) AS language,
  MAX(CASE WHEN setting = 'timezone' THEN value END) AS timezone
FROM user_settings
GROUP BY user_id
ORDER BY user_id;

-- Output:
-- user_id | theme | language | timezone
-- --------+-------+----------+--------------
-- 1       | dark  | en       | NULL
-- 2       | NULL  | de       | Europe/Berlin
-- 3       | light | NULL     | NULL