Pivot Tables and Their SQL Equivalents for Excel Users

Pivot tables are how most people first summarize data, and for many of them the first sign that the data has outgrown Excel is the day a pivot takes four minutes to refresh. At that point the same question has to be answered in SQL, by someone who has never written a GROUP BY with a rotated result. The reverse also happens: an analyst gets a query result and wants to explore it in a pivot. The two tools describe one operation, and the translation is mechanical once you have seen it.

This guide is the map between them. It starts with the mental model, builds one report in both forms, then covers the pieces that make a real report: dates, rotation in each major database, shares and ranks, calculated fields, and the source problems that cause wrong numbers. It ends with a way to decide where each calculation should live and a test plan to prove the two agree. The spokes linked throughout go deeper on each piece.

What a pivot table really is

A pivot table is a grouped aggregate with a rotation applied to the output. You choose which columns of the source identify a group, which column to summarize, and how. Excel then computes one number per group and arranges the groups on two axes.

SQL does the first half directly. GROUP BY names the identifying columns, an aggregate function summarizes, and the result is a list with one row per group. The second half, the rotation that puts some of the groups across the top as columns, is the part SQL does not do natively, because a result set must have a fixed list of columns when the query is parsed.

That gap explains almost every difference between the tools. A pivot adds a column when a new category appears; a SQL query does not unless you wrote it to. A pivot has drag-and-drop, subtotals and a grand total row; SQL returns rows you arrange. A pivot recalculates on refresh; a query reruns when you ask. Keep the gap in mind and the translations below will read as obvious.

The four areas and their SQL partners

The field list has four areas.

With only Rows and Values, the pivot is exactly a grouped query. Adding Columns is where the SQL grows. Treat the first case as the default and the second as an extra step. Many reports that people build as a two-way pivot are easier to use as a long table of three columns: the two groupings and the number, because a long table can be filtered, charted and loaded elsewhere with no further work.

One report in both forms

Start with a source table orders, one row per order line:

In Excel, insert a PivotTable from this range, drag region to Rows, category to Columns, and add a calculated field or a helper column for revenue (quantity times unit price) to Values. The pivot shows North with Books 60 and Games 60, South with Books 10 and Games 30, and totals of 70, 90 and 160.

In SQL, the equivalent long form is:

SELECT region,
       category,
       SUM(quantity * unit_price) AS revenue
FROM orders
GROUP BY region, category
ORDER BY region, category;

 region | category | revenue
--------+----------+---------
 North  | Books    |      60
 North  | Games    |      60
 South  | Books    |      10
 South  | Games    |      30

And the rotated form, which has one column per category:

SELECT region,
       SUM(quantity * unit_price) FILTER (WHERE category = 'Books') AS books,
       SUM(quantity * unit_price) FILTER (WHERE category = 'Games') AS games,
       SUM(quantity * unit_price)                                  AS total
FROM orders
GROUP BY region
ORDER BY region;

 region | books | games | total
--------+-------+-------+-------
 North  |    60 |    60 |   120
 South  |    10 |    30 |    40

Both give the same numbers. The first adapts to a new category; the second shows the layout people expect. A full walk through each field area, including ROLLUP for the totals, is in writing the same Excel pivot table as a GROUP BY query.

Getting the data into the pivot

A pivot is only as good as its source, and the source is where most effort goes. Three ways to feed one, from weakest to strongest:

1. Paste a range. Quick, and the pivot ignores rows added later. Fine for a one-off.

2. Use a Table. Pressing Ctrl+T makes the range a Table that grows when rows are added, and the pivot follows on refresh.

3. Use a query. Connect to the database or file with Power Query, write the SQL or the steps once, and let the pivot read the result. This is the route that survives month two (building a pivot table from a database query).

The query route also lets you do the work the database is good at, such as joining the customer table so region is a column, filtering to a period, and dropping columns the pivot does not use. The rule is to join, filter and clean in SQL, and let the pivot do the final grouping and layout. A query that returns one row per order line is the best shape: detailed enough to slice any way, and flat enough to need no further preparation.

When the data is larger than a worksheet, the sheet is the thing to leave out. Load to the Data Model, or aggregate before Excel sees it (making a pivot table from data larger than Excel allows). A good rule is that anything above a few hundred thousand rows should not be on a sheet at all.

Dates are the first complication

Almost every pivot has a date, and almost every date problem is in the source. Excel's Group command buckets dates into months, quarters and years in two clicks, and refuses with "Cannot group that selection" when the column holds text, blanks or errors. In SQL, the same buckets come from date_trunc('month', order_date) in PostgreSQL and similar functions elsewhere.

Fiscal years are where the two diverge from what people expect. Excel groups on the calendar and offers no fiscal setting, so a company whose year starts in April needs a helper column or a date table. In SQL it is a shifted date and an EXTRACT. In both cases the definition should be written down once and reused, because two reports with different Q1 boundaries cause arguments that nobody can resolve from the numbers alone (grouping dates by month or quarter in a pivot table).

Excel stores dates as serial numbers, and a CSV import can turn them into text or into a different month depending on locale. If a pivot shows 2025-03-04 as April, the cause is a day/month swap on import and the fix is in the load, not the pivot (Excel stores dates as serial numbers explains the format).

Rotating rows into columns

Rotation is the one step that differs by engine. The portable method is conditional aggregation, one aggregate per output column. PostgreSQL and SQLite prefer FILTER, and everyone else uses CASE. SQL Server also offers a PIVOT operator that takes the aggregate, the column to spread and the literal list of values. PostgreSQL adds crosstab() in the tablefunc extension.

The consequence is always the same: you must name the output columns. If categories change, either generate the statement from the distinct values or return the long form and let the client rotate. Generating SQL is safe only when identifiers and literals are quoted with the engine's own functions (format with %I and %L in PostgreSQL, QUOTENAME in SQL Server, QUOTE in MySQL), because a category value is data and can contain anything (pivoting rows into columns in PostgreSQL and in MySQL and SQL Server).

The reverse, turning a wide sheet into a long table, is unpivoting. It matters because Excel users tend to create a column per month, and databases and charts want the month as a value. PostgreSQL does it with CROSS JOIN LATERAL (VALUES ...), SQL Server with UNPIVOT or CROSS APPLY, DuckDB with UNPIVOT, MySQL with UNION ALL. Decide what a blank cell means before you choose, because UNPIVOT drops nulls and VALUES keeps them (unpivoting columns back into rows in SQL).

Shares, ranks and running totals

The pivot's Show Values As menu is a set of window functions with the formulas hidden.

Writing a window function over a grouped result takes a moment to get used to: the inner SUM(x) is the group total, and the outer SUM(...) OVER sums those totals across groups. Once learned, it covers every Show Values As option.

The traps are in the arithmetic. Integer division returns zero in PostgreSQL, SQL Server and SQLite, so multiply by 100.0 first. A zero denominator raises an error, so wrap it in NULLIF. And rounded percentages seldom add to exactly 100, so the display should either admit that or allocate the remainder (calculating percent of total in a pivot and in SQL).

Calculated fields and what they really compute

A pivot's calculated field applies its formula to the summed values, not to each row. A field Margin = Revenue - Cost is the sum of revenue minus the sum of cost, which for subtraction equals the sum of row margins. A ratio field Revenue / Units is the total revenue divided by total units, not the average of row ratios. Those are different numbers, and the first is usually the right one.

In SQL, write the aggregates first and then the arithmetic: SUM(revenue) / NULLIF(SUM(units), 0). The habit avoids a common mistake of averaging averages, where a group of two orders counts as much as a group of two thousand. When a distinct count is needed, use COUNT(DISTINCT customer_id). A plain pivot cannot compute it without the Data Model, which is one of the reasons to load to the model.

Sources of wrong numbers

When a pivot and a query disagree, the cause is nearly always upstream. The list, roughly in the order they occur:

• Numbers stored as text. The pivot counts them instead of summing, or silently skips them. Fix the column type in the source (fixing numbers stored as text in Excel).

• A range that stopped growing. Rows below the range are missing. Use a Table or a query.

• A blank or duplicate header. The pivot refuses to build or mislabels a field (pivot table refresh errors and missing fields).

• A join that multiplies rows. Joining orders to a table with duplicate keys repeats the order lines and inflates every sum. Check that the lookup key is unique (VLOOKUP and XLOOKUP versus SQL JOIN).

• A filter you forgot. A report filter, slicer or a pivot filter on a field hides rows. Check for the filter funnel icons.

• Inconsistent categories. Books, books and Books are three groups to a pivot, though SQL may fold case depending on collation. Normalize first (standardizing categories with a lookup table).

• Duplicate rows. An export that includes the same order twice doubles it. Count distinct keys before trusting a sum (finding duplicate rows in an Excel sheet with code).

None of these is a pivot bug. A habit of checking the source first saves hours of debugging in the layout.

The same job in pandas

Python users have the equivalent in pivot_table, which takes the same four pieces:

import pandas as pd

df = pd.read_excel("orders.xlsx")
df["revenue"] = df["quantity"] * df["unit_price"]

wide = df.pivot_table(
    index="region",
    columns="category",
    values="revenue",
    aggfunc="sum",
    margins=True,
    margins_name="Total",
)
print(wide)

category  Books  Games  Total
region
North        60     60    120
South        10     30     40
Total        70     90    160

index is Rows, columns is Columns, values and aggfunc are Values, and margins=True adds the grand totals. The reverse operation is melt, which unpivots. For the long form with no rotation, df.groupby(["region", "category"])["revenue"].sum(). A pivot in pandas adapts to new categories, like Excel's, which is the main reason to prefer it when the set of columns changes (working with Excel in pandas).

Where each calculation should live

The decision is less about capability and more about who maintains it and how often it runs. A practical split:

Use the pivot for what it does best, which is fast, visual exploration by someone who is looking. Use SQL for what must be repeatable, reviewable and shared. A report that matters should have its logic in a place that has version control and a name. A pivot with hand-formatted layout and a hidden filter is not that place.

Performance notes

A pivot is cheap on thousands of rows and unpleasant on millions, mostly because of memory and the cache it holds. If a refresh takes minutes, look at the query behind it before the pivot: return only the columns and rows needed, filter in SQL, and index the filter and join columns. A refresh that scans a billion-row table on every open is a database problem that Excel only reveals (slow Excel refresh from a database).

In SQL, a grouped query with many FILTER aggregates reads the table once. It is not ten times slower than one aggregate. The cost grows with the number of groups and the width of the result, not with the number of columns you rotate. Pre-aggregating into a summary table or a materialized view pays off when the same grouping is read many times, at the cost of refreshing it when the data changes.

A test plan for a pivot and its query

Before anyone relies on either form, check them against each other and against the source.

1. Row count. The source count, the query count and the number of rows the pivot's Count field reports should agree before any filter.

2. Grand total. The pivot's Grand Total and SELECT SUM(...) over the same filtered rows should match to the cent.

3. One subtotal. Pick a region and compare by hand with a filtered SUM.

4. One cell. Pick a single intersection and trace its rows in the source.

5. An empty case. Filter to a period with no data and confirm both tools show nothing or zero, in the way you intended.

6. A new category. Add a test row with a new category and refresh. The pivot should show it; the SQL pivot should show it only if designed to, and a catch-all column or a test should flag it.

7. A refresh after change. Rename a header in a copy and refresh, to see what breaks and whether the message is clear.

Keep the expected figures somewhere visible, such as a cell beside the pivot, so a refresh that changes them draws attention. For a recurring report, turn steps 1 and 2 into an automatic check that fails loudly.

Writing it down

A pivot does not document itself. Someone opening the file next year sees numbers and no definition. Write, in the workbook or beside the SQL, what each measure means (revenue is quantity times unit price, before tax and returns), which rows are excluded and why, which date field buckets the periods and where the fiscal year begins, and where the source is. A few lines are enough. The most expensive disagreements in reporting are not wrong arithmetic, they are two correct numbers with different definitions.

Name things after what they are. A field called Sum of Amount2 says nothing; Net revenue says what it is. Give the SQL the same names, so a reader can follow a figure from the sheet to the query.

A worked session from export to answer

Here is the sequence I would follow when handed a sales export and asked "how are we doing by region". It uses both tools, because each catches what the other misses.

Start by looking at the file before building anything. Open it, scroll to the bottom to see that the last row is a real row and not a total or a truncated line, check the header row for blanks, and filter each text column to see its distinct values. Five minutes here finds the North with a trailing space, the date column that is half text, and the three rows with no region. Fix what is cheap to fix and write down what is not.

Next, load the cleaned data as a Table or through a query, and build the first pivot with region in Rows and revenue in Values. This is exploration. Drag fields around, add category to Columns, add a date grouped by month, and look for the shape: which regions are big, which months have gaps, whether anything looks impossible. The pivot is fast enough for this that the exploration costs minutes.

When something looks interesting, write it as SQL. Take the pivot's grand total and a region subtotal and reproduce them with a grouped query against the original database or a DuckDB read of the file. If they agree, you have confirmed two things at once: that your reading of the pivot is right, and that the data in the file matches the data in the database. If they disagree, you found a source problem before it reached a slide.

Then decide what is recurring. A report that will be produced every month becomes a query with the filters and date logic written in it, and a pivot is built on the result for the people who want to slice. A question asked once stays in the pivot. The mistake to avoid is promoting a pivot into a process without ever writing its logic anywhere else, because the first person who changes a filter changes the report.

Finally, record the answer with its definition. "North revenue was 120 in Q1, defined as quantity times unit price before returns, from the orders table as of the export date" is a sentence that survives staff changes. A screenshot of a pivot is not.

Slicers, filters and drill down

Three interactive features of pivots deserve a SQL counterpart, because people use them without noticing they are changing the question.

A slicer is a filter you can see. Selecting two regions in a slicer is WHERE region IN ('North','South'). The risk is the same as with any filter: the totals now describe a subset, and percentages are computed against the visible subset. A slicer left on from yesterday is the most common reason two people see different numbers from the same file. Put the active filters in a visible text cell, or clear them before sending.

A report filter at the top of the pivot is the same idea in a quieter place. Pivots built from the Data Model also support filters on fields from related tables, which in SQL means a join and a WHERE on the joined column.

Drill down, double-clicking a number to see the rows behind it, is the pivot's best feature and has no direct SQL form. The SQL equivalent is the same WHERE that identifies the cell, run against the detail table: SELECT * FROM orders WHERE region = 'North' AND category = 'Books'. Learning to write that query by reading a pivot cell is a useful exercise. It forces you to name every condition the cell implies, including the date range and any filter in force.

Questions people ask, answered both ways

Several questions come up in nearly every team, and each has a pivot answer and a SQL answer worth knowing.

Which customers account for most of the revenue? In a pivot, customer in Rows, revenue in Values, sorted descending, with % of Grand Total beside it and a running total. In SQL, a grouped query with a window sum ordered by revenue. The query version is the one you can rerun as the data grows, and the pivot is the quicker way to find out whether the question is interesting.

How many distinct customers bought each month? A plain pivot cannot count distinct values unless the data is in the Data Model, where Distinct Count is available. In SQL, COUNT(DISTINCT customer_id) grouped by month. This is a good test of whether a pivot is the right tool: if you need distinct counts across several tables, the model or SQL is a better home.

Which products declined from last quarter? In a pivot, product in Rows, quarter in Columns, and Show Values As percent difference from the previous. In SQL, LAG over the quarter within each product. The SQL version needs care with products that did not exist last quarter, which divide by null and should be labeled new rather than left blank.

What is the average order value by region? The trap is averaging averages. A pivot set to Average of order total gives the mean of order totals, which is right if each row is an order. If each row is an order line, the mean is per line. Decide the grain first: sum revenue, count distinct orders, divide. In SQL, SUM(revenue) / COUNT(DISTINCT order_id).

Are there regions missing from the report? A pivot shows only what exists in the data. If a region had no sales, it is not a row, and a reader assumes it is not a region. In SQL, left join from a table of regions to the sales, and show zero. Reporting what is absent needs a list of what should be there.

Teaching the translation to a team

If a team is moving from spreadsheets toward SQL, the pivot is a good bridge because everyone already understands it. Sit someone with a working pivot and ask them to say it aloud as a sentence: "for each region and category, add up revenue". That sentence is the GROUP BY and the SUM. Then ask what rows are included, which is the WHERE. Then ask what is shown across the top, which is the part that needs the extra step. People who can say the sentence can write the query.

Give them a small table of their own data and the three queries from the worked example, and ask them to predict the output before running anything. Predicting is what builds the intuition, and it is the habit that catches wrong numbers later. Pair a pivot with its query on the first few reports, and let them compare. After a handful, most people stop building the pivot first.

When a pivot table or its SQL equivalent is the wrong answer

You need a record, not a summary. A pivot cannot tell you which orders broke a rule. For individual rows use a filter or a query.

The dimensions are not known in advance. Open-ended exploration with many fields favors a BI tool or notebook over a fixed query.

The data is sensitive. A pivot cache holds the source rows inside the workbook. Sending the file sends the data. If the audience should see only aggregates, send the aggregates.

The result must be exact for a regulator or auditor. Document the logic in SQL under version control, reproduce it on a copy of the data, and keep the output, not a pivot that anyone can drag around.

The calculation is not a summary. Matching records between two lists, de-duplicating, cleaning and joining are not pivot jobs. See cleaning Excel data before analysis and doing proper analysis on Excel data with SQL.

Where to go next

If you are moving a pivot into SQL, start with the field-by-field translation in writing the same Excel pivot table as a GROUP BY query, then the rotation post for your database. If a pivot is the thing breaking, begin with pivot table refresh errors and missing fields. For the wider move away from formulas, translating Excel formulas into SQL lists the other function-by-function equivalents, and Pivot tables versus GROUP BY and CASE compares the two approaches on a decision basis.

For the reference material, Microsoft's page on creating a PivotTable covers the interface, PostgreSQL documents grouping sets and window functions, and the pandas reference for pivot_table lists every argument. To format a long query, use the SQL formatter, and to turn a small result into JSON, the CSV to JSON converter.