SQL Query to Pivot Rows to Columns

If you are working with data in SQL, pivoting rows into columns is one of the most powerful transformations you will ever need.

Whether you are converting monthly sales records into a single summary row per customer, transforming survey responses into a wide-format report, reshaping log data for easier analysis, or building a dashboard-ready dataset — pivoting is how you go from a tall, narrow table to a wide, readable one.

In this guide, we will break down every major way to pivot rows to columns in SQL with clear, practical examples you can apply immediately across different database systems.

Setting Up the Example Dataset

We will use one consistent dataset throughout this guide.

sql

CREATE TABLE sales (
    employee    VARCHAR(50),
    quarter     VARCHAR(10),
    revenue     INT
);

INSERT INTO sales VALUES
('Alice', 'Q1', 15000),
('Alice', 'Q2', 18000),
('Alice', 'Q3', 21000),
('Alice', 'Q4', 17000),
('Bob',   'Q1', 12000),
('Bob',   'Q2', 14000),
('Bob',   'Q3', 11000),
('Bob',   'Q4', 16000),
('Carol', 'Q1', 19000),
('Carol', 'Q2', 22000),
('Carol', 'Q3', 24000),
('Carol', 'Q4', 20000);

Raw table (tall format):

employee	quarter	revenue
Alice	Q1	15000
Alice	Q2	18000
Alice	Q3	21000
Alice	Q4	17000
Bob	Q1	12000
…	…	…

Goal — pivot to wide format:

employee	Q1	Q2	Q3	Q4
Alice	15000	18000	21000	17000
Bob	12000	14000	11000	16000
Carol	19000	22000	24000	20000

Method 1: CASE WHEN

The CASE WHEN method works in every major SQL database — MySQL, PostgreSQL, SQLite, SQL Server, and BigQuery. It is the most portable pivot technique and the best one to learn first.

sql

SELECT
    employee,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY employee
ORDER BY employee;

Output:

employee	Q1	Q2	Q3	Q4
Alice	15000	18000	21000	17000
Bob	12000	14000	11000	16000
Carol	19000	22000	24000	20000

How it works: For each row, CASE WHEN checks which quarter it belongs to and places the revenue value in the correct column. SUM() with GROUP BY employee then collapses all rows for the same employee into one. Any quarter that does not match returns 0, keeping all columns populated.

Method 2: PIVOT Operator

SQL Server and Oracle support a native PIVOT operator that handles the transformation more concisely than CASE WHEN.

sql

-- SQL Server / Oracle syntax
SELECT employee, Q1, Q2, Q3, Q4
FROM sales
PIVOT (
    SUM(revenue)
    FOR quarter IN (Q1, Q2, Q3, Q4)
) AS pivot_table
ORDER BY employee;

Output:

employee	Q1	Q2	Q3	Q4
Alice	15000	18000	21000	17000
Bob	12000	14000	11000	16000
Carol	19000	22000	24000	20000

The PIVOT operator requires three things: an aggregate function (SUM), the column whose values become new column headers (FOR quarter), and the list of values to pivot (IN (Q1, Q2, Q3, Q4)).

This is cleaner than CASE WHEN but only available in SQL Server and Oracle — not MySQL, PostgreSQL, or SQLite.

Method 3: crosstab()

PostgreSQL does not support the PIVOT operator but provides the crosstab() function through the tablefunc extension, which achieves the same result.

sql

-- Enable the extension first (run once)
CREATE EXTENSION IF NOT EXISTS tablefunc;

-- Pivot using crosstab
SELECT *
FROM crosstab(
    'SELECT employee, quarter, revenue
     FROM sales
     ORDER BY employee, quarter',
    'SELECT DISTINCT quarter FROM sales ORDER BY quarter'
) AS ct (
    employee VARCHAR,
    Q1 INT,
    Q2 INT,
    Q3 INT,
    Q4 INT
);

Output:

employee	Q1	Q2	Q3	Q4
Alice	15000	18000	21000	17000
Bob	12000	14000	11000	16000
Carol	19000	22000	24000	20000

The first query inside crosstab() provides the source data sorted by row identifier and category. The second query provides the distinct category values. The AS ct(...) block defines the output column names and types.

Method 4: Dynamic Pivot

The methods above require you to hard-code the column values (Q1, Q2, Q3, Q4). When the values are not known in advance — for example, when pivoting by month names or product IDs — you need a dynamic pivot that builds the query at runtime.

Dynamic pivot in SQL Server:

sql

DECLARE @columns NVARCHAR(MAX) = '';
DECLARE @sql     NVARCHAR(MAX) = '';

-- Step 1: Build the list of column names dynamically
SELECT @columns = STRING_AGG(QUOTENAME(quarter), ', ')
FROM (SELECT DISTINCT quarter FROM sales) AS quarters;

-- Step 2: Build and execute the dynamic SQL
SET @sql = '
SELECT employee, ' + @columns + '
FROM sales
PIVOT (
    SUM(revenue)
    FOR quarter IN (' + @columns + ')
) AS pivot_table
ORDER BY employee;';

EXEC sp_executesql @sql;

Dynamic pivot in MySQL:

sql

SET @sql = NULL;

SELECT GROUP_CONCAT(
    DISTINCT CONCAT(
        'SUM(CASE WHEN quarter = ''', quarter,
        ''' THEN revenue ELSE 0 END) AS ', quarter
    )
) INTO @sql
FROM sales;

SET @sql = CONCAT(
    'SELECT employee, ', @sql,
    ' FROM sales GROUP BY employee ORDER BY employee'
);

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Dynamic pivots are more complex but essential when the number of distinct values in your pivot column changes over time.

Method 5: Pivot With Multiple Aggregates

Sometimes you need more than one aggregate per pivot column — for example, both total revenue and average revenue per quarter.

sql

-- Pivot with SUM and AVG side by side
SELECT
    employee,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1_total,
    AVG(CASE WHEN quarter = 'Q1' THEN revenue END)        AS Q1_avg,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2_total,
    AVG(CASE WHEN quarter = 'Q2' THEN revenue END)        AS Q2_avg,
    SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS Q3_total,
    AVG(CASE WHEN quarter = 'Q3' THEN revenue END)        AS Q3_avg,
    SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS Q4_total,
    AVG(CASE WHEN quarter = 'Q4' THEN revenue END)        AS Q4_avg
FROM sales
GROUP BY employee
ORDER BY employee;

Note that AVG uses NULL (no ELSE clause) instead of 0 — this prevents zero values from skewing the average for quarters with no data.

Method 6: Pivot With a WHERE Filter

You can combine pivoting with filtering to pivot only a subset of your data.

sql

-- Pivot only Q1 and Q2 for employees with total revenue above 30000
SELECT
    employee,
    SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1,
    SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS Q2
FROM sales
WHERE quarter IN ('Q1', 'Q2')
GROUP BY employee
HAVING SUM(revenue) > 30000
ORDER BY employee;

Output:

employee	Q1	Q2
Alice	15000	18000
Carol	19000	22000

Filtering before pivoting reduces the data volume and keeps the output focused on exactly what you need.

Pivot Methods

Method	Database Support	Dynamic?	Best For
CASE WHEN	All databases	No	Universal, portable pivots
PIVOT operator	SQL Server, Oracle	No	Clean syntax on supported DBs
crosstab()	PostgreSQL only	No	PostgreSQL-native pivot
Dynamic PIVOT	SQL Server, MySQL	Yes	Unknown or changing column values
Multiple aggregates	All databases	No	Several metrics per pivot column
Filtered pivot	All databases	No	Pivoting a subset of data

Real-World Use Cases

Sales Reporting — Monthly Revenue Per Product

sql

SELECT
    product,
    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 monthly_sales
GROUP BY product
ORDER BY product;

HR Analytics — Headcount by Department and Year

sql

SELECT
    department,
    SUM(CASE WHEN year = 2022 THEN headcount ELSE 0 END) AS "2022",
    SUM(CASE WHEN year = 2023 THEN headcount ELSE 0 END) AS "2023",
    SUM(CASE WHEN year = 2024 THEN headcount ELSE 0 END) AS "2024"
FROM headcount_data
GROUP BY department
ORDER BY department;

Survey Analysis — Response Count by Question and Rating

sql

SELECT
    question,
    SUM(CASE WHEN rating = 1 THEN 1 ELSE 0 END) AS rating_1,
    SUM(CASE WHEN rating = 2 THEN 1 ELSE 0 END) AS rating_2,
    SUM(CASE WHEN rating = 3 THEN 1 ELSE 0 END) AS rating_3,
    SUM(CASE WHEN rating = 4 THEN 1 ELSE 0 END) AS rating_4,
    SUM(CASE WHEN rating = 5 THEN 1 ELSE 0 END) AS rating_5
FROM survey_responses
GROUP BY question
ORDER BY question;

Common Mistakes to Avoid

Using ELSE 0 with AVG — When calculating averages, ELSE 0 pulls the average down for rows with no matching data. Use ELSE NULL (or no ELSE clause) so those rows are excluded from the average calculation:

sql

-- Wrong — zeros skew the average
AVG(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END)

-- Correct — nulls are excluded from AVG
AVG(CASE WHEN quarter = 'Q1' THEN revenue END)

Forgetting GROUP BY — Every pivot using CASE WHEN requires a GROUP BY on your row identifier. Without it, you get one column per row instead of one row per entity.

Hard-coding values that change — If new quarters, products, or categories are added to your data over time, hard-coded pivots silently miss them. Use a dynamic pivot for data that grows.

Using PIVOT on unsupported databases — The PIVOT operator does not exist in MySQL, PostgreSQL, or SQLite. If your database is not SQL Server or Oracle, use CASE WHEN instead.

Not aliasing pivot columns clearly — Column names like Q1 are fine for internal use, but in reports always use descriptive aliases:

sql

-- Unclear
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS Q1

-- Clear in a business report
SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS "Q1 Revenue ($)"

Pivoting rows to columns in SQL is one of those transformations that unlocks a completely different way of viewing your data. Once you master it, producing summary reports and wide-format datasets directly in SQL becomes fast and reliable.

Here is the simplest decision guide:

• Any database → CASE WHEN with SUM and GROUP BY
• SQL Server or Oracle → native PIVOT operator
• PostgreSQL → crosstab() via tablefunc extension
• Column values unknown at query time → dynamic pivot
• Multiple metrics per column → multiple aggregates with CASE WHEN
• Pivot a subset of data → add WHERE and HAVING clauses

Start with CASE WHEN — it runs everywhere and makes the logic completely transparent. Once that feels natural, explore the native PIVOT operator or crosstab() if your database supports them. For production pipelines where data shape changes over time, invest in dynamic pivots so your queries never go stale.

FAQs