How to Pivot in SQL

Use pivoting when row values should become separate columns in the result. This is perfect for reporting tables, dashboards, monthly summaries, and cross-tab style exports.

What you’ll build or solve

You’ll learn how to pivot in SQL using conditional aggregation, which works across most database systems. You’ll also know when native PIVOT syntax may be available.

When this approach works best

This approach is the right choice when categories should display side by side as columns.

Common real-world scenarios include:

• Revenue by month
• Scores by subject
• Orders by status columns
• Sales by country
• KPI dashboard tables

This is a bad idea when categories are dynamic and unknown at query-writing time without dynamic SQL.

Prerequisites

You only need:

• Basic GROUP BY
• Familiarity with CASE
• A column that contains category values

Step-by-step instructions

Step 1: Use conditional aggregation

The most portable pivot pattern uses SUM(CASE ...).

SELECT
  country,
  SUM(CASE WHEN month = 'Jan' THEN revenue ELSE 0 END) AS jan,
  SUM(CASE WHEN month = 'Feb' THEN revenue ELSE 0 END) AS feb,
  SUM(CASE WHEN month = 'Mar' THEN revenue ELSE 0 END) AS mar
FROM sales
GROUP BY country;

This turns month rows into columns.

Each CASE branch fills one output column.

This works in PostgreSQL, MySQL, SQL Server, and most other engines.

What to look for:

• CASE creates one column per category
• GROUP BY defines the row dimension
• Great for dashboards and exports
• Works across most databases
• Dynamic categories need dynamic SQL

Examples you can copy

Orders by status

SELECT
  user_id,
  SUM(CASE WHEN status = 'paid' THEN 1 ELSE 0 END) AS paid_orders,
  SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY user_id;

Scores by subject

SELECT
  student_id,
  MAX(CASE WHEN subject = 'Math' THEN score END) AS math_score,
  MAX(CASE WHEN subject = 'Science' THEN score END) AS science_score
FROM exams
GROUP BY student_id;

Revenue by region

SELECT
  year,
  SUM(CASE WHEN region = 'USA' THEN total ELSE 0 END) AS usa_total,
  SUM(CASE WHEN region = 'UK' THEN total ELSE 0 END) AS uk_total
FROM revenue
GROUP BY year;

Common mistakes and how to fix them

Mistake 1: Forgetting the grouping dimension

What the reader might do:

SELECT
  SUM(CASE ...)
FROM sales;

Why it breaks: all rows collapse into one giant pivot row.

Corrected approach:

Add the row dimension in GROUP BY.

Mistake 2: Using dynamic categories as static columns

What the reader might do:

Hardcode categories that change every week.

Why it breaks: the query becomes brittle.

Corrected approach:

Use dynamic SQL or pivot in the BI layer.

Mistake 3: Wrong aggregate choice

What the reader might do:

SUM(CASE WHEN subject = 'Math' THEN score END)

Why it breaks: repeated rows may inflate values when only one score should exist.

Corrected approach:

Use MAX() for single-value cells.

Troubleshooting

If all data lands in one row, add GROUP BY.

If values are inflated, choose the correct aggregate.

If categories change often, use dynamic SQL.

If the database supports native PIVOT, compare readability.

Quick recap

• Pivot rows into columns with SUM(CASE ...)
• Use GROUP BY for row dimensions
• Pick the correct aggregate
• Great for reports and dashboards
• Use dynamic SQL for changing categories