All 40 lessons

Lesson 39 of 40 · SQL for Analysts

SQL Pivot: Rows to Columns

By Skillancy Editorial Team

Every example runs on the ShopKart dataset, already loaded in the free SQL Compiler. Click Try it in the SQL Compiler under a query to open it there, then press Run.

Pivoting turns row values into columns, like an Excel pivot table. Instead of one row per (month, category), you get one row per month with a column for each category. In SQL, the portable way to pivot is conditional aggregation: an aggregate wrapped around a CASE (or FILTER) for each output column.

Syntax

SELECT group_column,
       SUM(CASE WHEN pivot_column = 'A' THEN value END) AS a,
       SUM(CASE WHEN pivot_column = 'B' THEN value END) AS b
FROM table_name
GROUP BY group_column;

PostgreSQL also supports the shorter SUM(value) FILTER (WHERE pivot_column = 'A').

Examples

1. Long format (before pivoting)

Order counts by payment method and status, one row per combination:

SELECT payment_method, status, COUNT(*) AS orders
FROM orders
GROUP BY payment_method, status
ORDER BY payment_method, status;

Try it in the SQL Compiler

Result:

payment_method	status	orders
COD	Delivered	2
COD	Pending	1
COD	Returned	1
Card	Cancelled	1
Card	Delivered	4
Card	Returned	1
Card	Shipped	1
NetBanking	Delivered	2
UPI	Cancelled	1
UPI	Delivered	8
UPI	Pending	1
UPI	Shipped	1

(12 rows)

Accurate, but hard to scan.

2. Pivot status into columns

SELECT payment_method,
       COUNT(*) FILTER (WHERE status = 'Delivered') AS delivered,
       COUNT(*) FILTER (WHERE status = 'Shipped')   AS shipped,
       COUNT(*) FILTER (WHERE status = 'Pending')   AS pending,
       COUNT(*) FILTER (WHERE status = 'Cancelled') AS cancelled,
       COUNT(*) FILTER (WHERE status = 'Returned')  AS returned,
       COUNT(*) AS total
FROM orders
GROUP BY payment_method
ORDER BY total DESC;

Try it in the SQL Compiler

Result:

payment_method	delivered	shipped	pending	cancelled	returned	total
UPI	8	1	1	1	0	11
Card	4	1	0	1	1	7
COD	2	0	1	0	1	4
NetBanking	2	0	0	0	0	2

(4 rows)

Now it reads like a report. One glance shows UPI carries nearly half of all orders, and that both returns came through Card and COD.

3. The portable CASE version

Works in MySQL, SQL Server, BigQuery and every other database:

SELECT payment_method,
       SUM(CASE WHEN status = 'Delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status IN ('Cancelled', 'Returned') THEN 1 ELSE 0 END) AS lost
FROM orders
GROUP BY payment_method
ORDER BY payment_method;

Try it in the SQL Compiler

Result:

payment_method	delivered	lost
COD	2	1
Card	4	2
NetBanking	2	0
UPI	8	1

(4 rows)

4. Quarterly revenue by category

A typical management report: categories down the side, quarters across the top.

WITH lines AS (
  SELECT cat.category_name,
         EXTRACT(QUARTER FROM o.order_date) AS qtr,
         oi.quantity * oi.unit_price * (1 - oi.discount_pct / 100) AS net
  FROM order_items oi
  JOIN orders o       ON o.order_id = oi.order_id
  JOIN products p     ON p.product_id = oi.product_id
  JOIN categories cat ON cat.category_id = p.category_id
  WHERE o.status = 'Delivered'
    AND o.order_date >= '2025-01-01' AND o.order_date < '2026-01-01'
)
SELECT category_name,
       ROUND(COALESCE(SUM(net) FILTER (WHERE qtr = 1), 0)) AS q1,
       ROUND(COALESCE(SUM(net) FILTER (WHERE qtr = 2), 0)) AS q2,
       ROUND(COALESCE(SUM(net) FILTER (WHERE qtr = 3), 0)) AS q3,
       ROUND(COALESCE(SUM(net) FILTER (WHERE qtr = 4), 0)) AS q4,
       ROUND(SUM(net)) AS total_2025
FROM lines
GROUP BY category_name
ORDER BY total_2025 DESC;

Try it in the SQL Compiler

Result:

category_name	q1	q2	q3	q4	total_2025
Electronics	51389	1299	0	26696	79384
Fashion	8074	9648	1899	0	19621
Home & Kitchen	1647	4086	3997	0	9730
Beauty	3492	0	3393	597	7482
Sports	0	1438	799	799	3036
Books	0	948	0	1597	2545

(6 rows)

COALESCE(..., 0) shows 0 instead of NULL for quarters with no sales.

5. Percentages in a pivot

Share of each customer's orders paid by UPI vs other methods:

SELECT customer_id,
       COUNT(*) AS orders,
       ROUND(100.0 * COUNT(*) FILTER (WHERE payment_method = 'UPI') / COUNT(*), 0) AS upi_pct,
       ROUND(100.0 * COUNT(*) FILTER (WHERE payment_method <> 'UPI') / COUNT(*), 0) AS other_pct
FROM orders
GROUP BY customer_id
HAVING COUNT(*) >= 3
ORDER BY customer_id;

Try it in the SQL Compiler

Result:

customer_id	orders	upi_pct	other_pct
1	4	50	50
3	3	67	33
4	3	33	67

(3 rows)

Unpivot: columns to rows

Sometimes data arrives wide and you need it long, for example a targets sheet with one column per quarter:

CREATE TABLE category_targets (category_name VARCHAR(50), q1 INT, q2 INT, q3 INT, q4 INT);
INSERT INTO category_targets VALUES
('Electronics', 50000, 40000, 30000, 60000),
('Fashion', 10000, 8000, 12000, 15000);

SELECT t.category_name, v.quarter, v.target
FROM category_targets t
CROSS JOIN LATERAL (VALUES ('Q1', t.q1), ('Q2', t.q2), ('Q3', t.q3), ('Q4', t.q4)) AS v(quarter, target)
ORDER BY t.category_name, v.quarter;

Try it in the SQL Compiler

Result:

category_name	quarter	target
Electronics	Q1	50000
Electronics	Q2	40000
Electronics	Q3	30000
Electronics	Q4	60000
Fashion	Q1	10000
Fashion	Q2	8000
Fashion	Q3	12000
Fashion	Q4	15000

(8 rows)

CROSS JOIN LATERAL (VALUES ...) produces one row per listed column. The portable alternative is a UNION ALL of one SELECT per column.

Common mistakes

COUNT(CASE WHEN ... THEN 1 ELSE 0 END). COUNT counts the zeros too. Use SUM(...) with ELSE 0, or COUNT(CASE WHEN ... THEN 1 END) with no ELSE.
Forgetting new values. A hard-coded pivot silently ignores a new status like 'Refunded'. Add a total column to spot gaps.
Pivoting too early. Keep data long for calculations and joins; pivot only at the final reporting step.

Practice question

Build a table with one row per city and columns for the number of Prime and non-Prime customers.

Show solution

SELECT city,
       COUNT(*) FILTER (WHERE is_prime)     AS prime,
       COUNT(*) FILTER (WHERE NOT is_prime) AS non_prime
FROM customers
GROUP BY city
ORDER BY prime DESC, city
LIMIT 6;

Try it in the SQL Compiler

Result:

city	prime	non_prime
Bengaluru	1	1
Delhi	1	0
Hyderabad	1	0
Mumbai	1	0
Pune	1	0
Ahmedabad	0	1

(6 rows)

Key takeaways

Pivot in SQL with conditional aggregation: one SUM(CASE ...) or FILTER per output column.
COALESCE turns empty cells into 0.
Output columns must be known when you write the query.
Unpivot with CROSS JOIN LATERAL (VALUES ...) or UNION ALL.

Frequently asked questions

Does PostgreSQL have a PIVOT function?

Not built in. Use conditional aggregation (SUM(CASE WHEN ...) or FILTER), which works in every database. The tablefunc extension adds crosstab(), and SQL Server and Snowflake have a PIVOT keyword.

How do I pivot when I don't know the column values in advance?

SQL needs the output columns fixed when the query is written. For dynamic pivots, generate the query text with a script or stored procedure, or pivot in the BI tool or spreadsheet instead.

What is unpivoting?

The reverse of pivoting: turning several columns (such as jan_sales, feb_sales) into rows with a label column and a value column. It is often needed before charting or loading data.

← PreviousDatabase Normalization (1NF, 2NF, 3NF)Next →SQL Interview Questions for Data Analysts

Newsletter

Keep learning SQL

One email a week with SQL and analytics guides, new practice questions and cohort dates. No spam, unsubscribe anytime.

Prefer a one-page summary? Download the free SQL cheat sheet (PDF)