How to make a pivot table from a CSV with SQL

A pivot table answers questions like "how much did each category sell, by month?" You can get the same answer from a CSV with a few lines of SQL, and SQL keeps working when the file is too slow or messy for a spreadsheet. Synth runs these queries in your browser, with nothing to install and without uploading your file.

The examples below use a table called sales with the columns order_date, region, category and amount. Swap in your own column names.

Step 1: Load the CSV

Open Synth and drop your CSV onto the page. It becomes a SQLite table named after the file, so sales.csv becomes sales. No account is needed.

Step 2: Totals by one field

The simplest pivot puts one field in rows and sums a number. In SQL that's a GROUP BY:

SELECT category,
       SUM(amount) AS total
FROM sales
GROUP BY category
ORDER BY total DESC;

Step 3: Two fields in rows

Add more fields to the GROUP BY to nest them, the way a pivot table stacks row labels. You can show several values at once, like a sum and a count:

SELECT region, category,
       SUM(amount) AS total,
       COUNT(*)    AS orders
FROM sales
GROUP BY region, category
ORDER BY region, total DESC;

Step 4: Put values across the top as columns

This is the part people usually mean by "pivot." SQLite has no PIVOT keyword, so each column is a SUM(CASE WHEN ...) that only adds rows matching that column. Here months go across the top:

SELECT category,
       SUM(CASE WHEN strftime('%m', order_date) = '01' THEN amount ELSE 0 END) AS jan,
       SUM(CASE WHEN strftime('%m', order_date) = '02' THEN amount ELSE 0 END) AS feb,
       SUM(CASE WHEN strftime('%m', order_date) = '03' THEN amount ELSE 0 END) AS mar,
       SUM(amount) AS total
FROM sales
GROUP BY category
ORDER BY total DESC;

strftime('%m', order_date) pulls the month out of a date stored like 2026-01-15. If your dates look different, ask Synth's AI assistant to convert them, or pivot on a text column such as region instead.

Step 5: Percent of total

A window function divides each group by the grand total, like "Show values as % of grand total" in a spreadsheet:

SELECT category,
       SUM(amount) AS total,
       ROUND(100.0 * SUM(amount) / SUM(SUM(amount)) OVER (), 1) AS pct_of_total
FROM sales
GROUP BY category
ORDER BY total DESC;

Step 6: Add a grand total row

SELECT 'All categories' AS category, SUM(amount) AS total FROM sales
UNION ALL
SELECT category, SUM(amount) FROM sales GROUP BY category;

Save or chart the result

Click Download CSV above the results to save the pivot. With a free account you can also turn any result into a chart and export it to Excel with the chart included.

Don't want to write SQL?

Sign in and ask the AI assistant something like "total sales by category with one column per month." It writes the query for you, using only your column names, never your data.

Frequently asked questions