Pivot Tables Versus GROUP BY and CASE in SQL Compared

The pivot table is the most loved feature in Excel, and for good reason: drag a field to the side, drag another across the top, and a summary appears. Reproducing one in SQL is straightforward and teaches you what the pivot was doing. This post rebuilds a pivot on a small dataset with GROUP BY and conditional aggregation, adds the totals and percentages a pivot gives for free, and explains the one real difference, which is how columns are chosen. It is a spoke of translating Excel formulas into SQL.

The data and the pivot

WITH sales(region, product, amount) AS (
  VALUES
    ('North', 'pen',      20.00),
    ('North', 'notebook', 35.00),
    ('South', 'pen',      15.00),
    ('South', 'notebook', 60.00),
    ('North', 'pen',      25.00),
    ('East',  'pen',      10.00)
)

A pivot table with region in rows, product in columns and the sum of amount in the values looks like this:

Every cell is a sum over the rows that have that region and that product. The row and column totals are sums over one dimension, and the corner is the sum over everything.

GROUP BY with conditional columns

The query that produces the body of the table groups by the row field and computes one aggregate per column value, the same technique as in SUMIF and SUMIFS versus GROUP BY and IF and IFS versus CASE WHEN:

SELECT region,
       sum(amount) FILTER (WHERE product = 'notebook') AS notebook,
       sum(amount) FILTER (WHERE product = 'pen')      AS pen,
       sum(amount)                                      AS total
FROM sales
GROUP BY region
ORDER BY region;

 region | notebook |  pen  | total
--------+----------+-------+-------
 East   |          | 10.00 | 10.00
 North  |    35.00 | 45.00 | 80.00
 South  |    60.00 | 15.00 | 75.00

East has no notebook sales, so the sum is NULL, shown as blank, which matches the empty cell in the pivot. If you want zeros, wrap in COALESCE(..., 0). Without FILTER, use sum(CASE WHEN product = 'notebook' THEN amount END), which works on every engine. The structure is the whole trick: each output column is a sum restricted by a condition on the field that would sit across the top.

Totals with ROLLUP

A pivot adds subtotals and a grand total by default. SQL asks for them with ROLLUP:

SELECT region,
       sum(amount) AS total
FROM sales
GROUP BY ROLLUP (region)
ORDER BY region NULLS LAST;

 region | total
--------+--------
 East   |  10.00
 North  |  80.00
 South  |  75.00
        | 165.00

The final row, with a null region, is the grand total. To label it and to tell it apart from a genuine null region, use GROUPING:

SELECT CASE WHEN GROUPING(region) = 1 THEN 'Grand total' ELSE region END AS region,
       sum(amount) AS total
FROM sales
GROUP BY ROLLUP (region)
ORDER BY GROUPING(region), region;

GROUPING(region) is 1 for the rollup row and 0 for ordinary rows. With two row fields, ROLLUP (region, product) adds a subtotal for each region and a grand total, and GROUPING SETS lets you choose exactly which combinations to compute, which is what a pivot with several levels of rows does. SQL Server has WITH ROLLUP, MySQL has WITH ROLLUP too, and the standard syntax above works in PostgreSQL, DuckDB, SQLite (recent versions lack it) and others; check your engine.

The column totals, the bottom row of the pivot, need a UNION ALL with a second query or a GROUPING SETS ((region), ()) combined with conditional columns. Often it is simpler to compute them in the reporting layer, where adding a row is trivial.

Percentages: window functions

A pivot can show values as a percent of the grand total, of the row or of the column. The window functions behind this are covered in running totals in Excel versus window functions. In SQL, divide the cell by a window sum over the grouped rows:

SELECT region,
       sum(amount) AS total,
       round(100 * sum(amount) / sum(sum(amount)) OVER (), 1) AS pct_of_total
FROM sales
GROUP BY region
ORDER BY region;

 region | total | pct_of_total
--------+-------+--------------
 East   | 10.00 |          6.1
 North  | 80.00 |         48.5
 South  | 75.00 |         45.5

sum(sum(amount)) OVER () sums the grouped totals across all rows of the result: 165.00. Each region's total divided by it gives the share. The nested sum looks odd and is normal: the inner one is the aggregate for the group, the outer one is the window over the groups. For percent of row in a two-way table, partition the window by the row field. The percentages sum to 100.1 here because each is rounded; the pivot's "percent of grand total" has the same display rounding.

Dynamic columns: the real difference

A pivot table's columns follow the data. Add a product stapler to the source, refresh, and a new column appears. The SQL above has notebook and pen written into the query, and a new product would be missing from the output without any error. That is the genuine trade-off. There are four ways to handle it:

Return long form. Group by region and product and let the reporting tool pivot:

SELECT region, product, sum(amount) AS total
FROM sales
GROUP BY region, product
ORDER BY region, product;

This gives one row per combination: five rows here. It works for any number of products, loads straight into a BI tool or back into a pivot table in Excel, and needs no change when the categories change. For many uses it is the best output, and it is how data should travel between systems.

Generate the query. Build the list of products first (SELECT DISTINCT product FROM sales) and construct the FILTER columns in code. This is fine in an application that runs the query, and awkward to maintain in a stored view. Guard against injection by only using values from your own data, quoted properly.

Use a pivot operator. DuckDB has a PIVOT statement that does the column discovery for you:

PIVOT sales ON product USING sum(amount) GROUP BY region;

SQL Server's PIVOT operator needs the columns listed (FOR product IN ([notebook], [pen])), and PostgreSQL has the crosstab function in the tablefunc extension, which also needs a declared output shape. Snowflake and BigQuery have PIVOT clauses with variants that can derive the list in newer versions. Check what your engine offers.

Accept the fixed list and test it. If the products are a controlled vocabulary, a fixed column list is right: it documents the report. Add a check that fails when the data contains a product that is not in the list: SELECT product FROM sales WHERE product NOT IN ('notebook', 'pen') should return nothing.

Filters, slicers and the page area

A pivot table's filter area, slicers and timelines restrict the source rows. In SQL these are WHERE conditions, ideally parameters, applied before the grouping. A filter on a date range, for example, is WHERE sold >= :start AND sold < :end. The difference is that in Excel a person changes them interactively, and in SQL somebody edits or parameterizes the query. For a report that people explore, an interactive tool (a BI dashboard, or the pivot itself on top of a SQL result) is the better front end; the SQL's job is to provide clean, grouped data.

Pivot-specific behaviors to reproduce or drop

Pivot tables do several things silently that a translation has to decide on.

Sorting. Rows sort alphabetically by default, with manual overrides that persist. Write an explicit ORDER BY.

Blanks. A blank in the source field appears as (blank) in the pivot. SQL groups nulls into one group; label it with COALESCE(region, '(blank)').

Date grouping. Dragging a date field into rows can group by year, quarter and month automatically. In SQL, use date_trunc('month', sold) or extract(year from sold) explicitly.

Calculated fields. A pivot's calculated field is a formula over the summed values (it sums first, then calculates), which can differ from summing a per-row calculation. Translate it as an expression over the aggregates.

Stale data. A pivot shows the data as of its last refresh. A query is as fresh as its run.

Verify

Check that the grouped totals add up to the grand total, that the number of source rows (count(*)) equals the sum of row counts per group, and that each cell matches the pivot's cached value. A pivot's cache is in the workbook and can be read, but the cells are the simplest comparison.

When a pivot table is the better tool

People explore the data. Dragging fields to find a pattern is what a pivot is for.

The categories change constantly. A query with a fixed column list needs maintenance; the pivot adapts. Long-form output solves it where a tool can pivot.

The audience lives in Excel. Give them the long-form data and let them pivot.

The result is a one-off. If you will look at it once, the pivot is quicker than writing the query.

Excel's feature is described in Microsoft's guide to creating a PivotTable, and PostgreSQL documents ROLLUP in its grouping sets reference. To check a small extract as JSON, use the CSV to JSON converter.