How to Build a Pivot Table in SQLite

A pivot table transposes data: values that live in rows become column headers. Some databases have a dedicated PIVOT keyword (SQL Server, Oracle). SQLite does not. You build pivots manually with conditional aggregation, which is more verbose but works in every SQLite build and is genuinely portable across most SQL engines.

The examples use a sales table:

CREATE TABLE sales (
  product TEXT,
  year INTEGER,
  income REAL
);
 
INSERT INTO sales (product, year, income) VALUES
  ('Widget', 2022, 100),
  ('Widget', 2023, 140),
  ('Gadget', 2022,  80),
  ('Gadget', 2023, 120);

The data is in long form (one row per product-year). The goal is wide form: one row per product, one column per year.

The starting point: a grouped aggregate

Before pivoting, the natural query is a GROUP BY that keeps the result vertical:

SELECT product, year, SUM(income) AS income
FROM sales
GROUP BY product, year;

That returns one row per product-year. Pivoting collapses the year dimension into columns.

Method 1: CASE inside an aggregate

The classic approach wraps a CASE expression in SUM (or another aggregate). Each target column gets its own CASE that contributes a value only for the matching year:

SELECT
  product,
  SUM(CASE WHEN year = 2022 THEN income ELSE 0 END) AS "2022",
  SUM(CASE WHEN year = 2023 THEN income ELSE 0 END) AS "2023"
FROM sales
GROUP BY product;

Result:

product  2022  2023
-------  ----  ----
Gadget    80   120
Widget   100   140

The ELSE 0 means missing combinations show as 0. If you'd rather see NULL for "no data," drop the ELSE clause entirely, because a CASE with no matching branch returns NULL, and SUM ignores nulls.

Method 2: the FILTER clause

Since SQLite 3.30.0 (released October 2019), aggregates accept a FILTER clause. It expresses the same idea more clearly than CASE:

SELECT
  product,
  SUM(income) FILTER (WHERE year = 2022) AS "2022",
  SUM(income) FILTER (WHERE year = 2023) AS "2023"
FROM sales
GROUP BY product;

This produces the same result as the CASE version. The difference is readability: the condition sits beside the aggregate instead of being buried in a CASE. With FILTER, unmatched rows produce NULL (not 0), since the aggregate simply sees no rows for that filter. Wrap it in COALESCE(..., 0) if you want zeros:

SELECT
  product,
  COALESCE(SUM(income) FILTER (WHERE year = 2022), 0) AS "2022",
  COALESCE(SUM(income) FILTER (WHERE year = 2023), 0) AS "2023"
FROM sales
GROUP BY product;

Use FILTER on any build at 3.30.0 or newer. Fall back to CASE if you must support older SQLite.

Counting instead of summing

Pivots aren't only for sums. To count occurrences, swap in COUNT:

SELECT
  product,
  COUNT(*) FILTER (WHERE year = 2022) AS sales_2022,
  COUNT(*) FILTER (WHERE year = 2023) AS sales_2023
FROM sales
GROUP BY product;

COUNT(*) with a FILTER counts matching rows. AVG, MIN, and MAX work the same way.

Adding a total column

Because the pivot is a normal aggregate query, you can add a plain unfiltered aggregate alongside the pivoted ones:

SELECT
  product,
  SUM(income) FILTER (WHERE year = 2022) AS "2022",
  SUM(income) FILTER (WHERE year = 2023) AS "2023",
  SUM(income) AS total
FROM sales
GROUP BY product;

The dynamic column problem

Both methods share a limitation: you must list every output column by hand. If a new year appears in the data, the query won't pick it up until you edit it. SQLite has no built-in way to generate columns dynamically from data, because the column list of a result set must be fixed when the statement is prepared.

There are two practical workarounds:

1. Generate the SQL in your application. Query the distinct values first (SELECT DISTINCT year FROM sales ORDER BY year), then build the pivot statement as a string in your host language and execute it. This is the standard solution for genuinely dynamic pivots.

2. Keep the data long and pivot in the presentation layer. Many BI tools, dataframe libraries (pandas pivot_table, for example), and spreadsheet exports pivot far more flexibly than SQL. If the consumer can pivot, leave SQLite to do the aggregation and ship long-form rows.

If you only need a one-off pivot for analysis, generating the column list by hand from a quick SELECT DISTINCT is usually faster than building dynamic SQL.

Common mistakes

• Forgetting GROUP BY product. Without it the CASE/FILTER aggregates collapse the whole table into a single row.
• Quoting numeric column aliases incorrectly. A column named 2022 must be quoted ("2022"), because a bare number isn't a valid identifier.
• Confusing 0 and NULL for missing cells. CASE ... ELSE 0 gives zeros; FILTER gives nulls. Pick the one that matches how downstream code treats absent data.
• Expecting a PIVOT keyword. It doesn't exist in SQLite. Conditional aggregation is the supported path.

When you're shaping an exploratory pivot and don't yet know which dimensions to break out, Mako's AI autocomplete can scaffold the FILTER clauses from your column values so you can adjust them in place.

Mako connects to SQLite and eight other databases with AI-powered autocomplete. Try it free at mako.ai.