{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"pivoting-rows-to-columns","title":"Wide and long: pivoting rows to columns and back","url":"https://addaly.com/learn/data-and-sql/pivoting-rows-to-columns","position":32,"of":66,"minutes":9,"answer":"Store and compute in long form and pivot only for display, because a query's columns are fixed at plan time and cannot be discovered from the data.","keyIdea":"Store and compute in long form and pivot only for display, because a query's columns are fixed at plan time and cannot be discovered from the data, and a wide table needs a schema change for every new period."},"body":{"format":"markdown","text":"## Two shapes for the same facts\n\nA revenue-by-city-by-month result can be long, with one row per city per month and three columns, or wide, with one row per city and a column for each month. Long is how a database stores and groups facts. Wide is how a human reads them across a page. Most reporting is the act of turning one into the other, and most of the friction comes from asking SQL to do the human-shaped half.\n\n## Long to wide, when you know the columns\n\nThe tool is conditional aggregation, which the CASE lesson introduced: one aggregate per output column, each filtered to its slice.\n\n```sql\nSELECT city,\n       sum(amount) FILTER (WHERE month = '2026-01-01') AS jan,\n       sum(amount) FILTER (WHERE month = '2026-02-01') AS feb,\n       sum(amount) FILTER (WHERE month = '2026-03-01') AS mar\nFROM monthly_sales\nGROUP BY city;\n```\n\nOr `sum(CASE WHEN month = '2026-01-01' THEN amount END)` where FILTER is unsupported. A city with no sales in February gets NULL in that column, and `coalesce(..., 0)` decides whether the report shows a blank or a zero, which is a decision about meaning, not formatting.\n\n## Why the columns must be typed by hand\n\nYou will want to write \"one column per month, whatever months exist\". SQL cannot do it, and the reason is structural rather than a missing feature. A query's output columns are part of its type, fixed when the query is planned, before any row is read. The engine has to know there will be a column called `apr` before it can decide how to produce one. Data-driven columns would mean the shape of the result depends on the result, which the planner cannot resolve.\n\nThe escape hatches confirm the rule. Postgres's `crosstab()` in the `tablefunc` extension still needs the output column list declared in the call. DuckDB's `PIVOT monthly_sales ON month USING sum(amount)` discovers the months, and does it by running a first query to list them and then building the second query from that list; BigQuery's `PIVOT` requires the values in the statement. Where a report must pivot on values that change, the usual answer is to pivot in the reporting tool, or to generate the SQL from a query of the distinct values, which is what DuckDB does for you.\n\n## Wide to long\n\nThe reverse arrives whenever a spreadsheet lands in the database: `city, jan, feb, mar` as four columns. Grouping by month is impossible in that shape. Unpivot it:\n\n```sql\nSELECT city, month, amount\nFROM wide_sales\nCROSS JOIN LATERAL (VALUES ('2026-01-01', jan), ('2026-02-01', feb), ('2026-03-01', mar))\n  AS v(month, amount);\n```\n\nEach source row becomes three rows, one per month. The portable version is a `UNION ALL` of three `SELECT city, '2026-01-01', jan FROM wide_sales` queries. DuckDB has `UNPIVOT wide_sales ON jan, feb, mar INTO NAME month VALUE amount`, which reads as what it does.\n\n## Why wide is the wrong storage shape\n\nStore the wide table and every new month is an `ALTER TABLE` to add a column, every query that groups by time has to be rewritten, and the table is mostly NULL for any city that started trading recently. Store long, and a new month is just more rows. The spreadsheet habit of one column per period is exactly the habit the first lesson of this course described as a spreadsheet stopping working, and the fix is the same: long in the table, wide only at the moment of display.\n\n## The free path\n\nA pivot table in LibreOffice Calc or Google Sheets performs this operation with a drag, and for a one-off report on a few thousand rows it is the right tool. Export the long result as CSV, open it, and pivot there. Keep the SQL that produced the long result, because the pivot is display and the query is the record of what the numbers mean.\n\n## The habit\n\nWrite the query long, check the numbers long, and pivot last, in the query only when the column list is fixed, otherwise in the tool that shows the result. A wide result whose columns you had to type is a report; a wide table somebody stores is a problem waiting for its thirteenth month."},"check":{"question":"A monthly pivot query uses one `sum(amount) FILTER (WHERE month = ...)` per month. April's sales are in the table, but the report has no April column. What is the direct cause?","options":[{"text":"FILTER ignores months that were absent when the table was created, so April needs a schema refresh before the expression can see it.","correct":false,"tempting":"FILTER evaluates a condition per row at run time; nothing about the table's creation date affects it."},{"text":"The query names its output columns by hand, and nobody added an April expression, so the April rows are read and then discarded.","correct":true},{"text":"April's rows are still NULL-typed because they arrived after the query was last planned.","correct":false,"tempting":"Rows are not typed by arrival time, and re-planning happens on every execution."},{"text":"GROUP BY city collapsed April into the March column because both fall in the same quarter.","correct":false,"tempting":"Each FILTER tests an exact month; a quarter grouping would need to be written explicitly."}]},"prev":{"slug":"rollups-and-subtotals","title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","url":"https://addaly.com/learn/data-and-sql/rollups-and-subtotals"},"next":{"slug":"reconciling-a-total","title":"Reconciling: making a total tie out before you send it","url":"https://addaly.com/learn/data-and-sql/reconciling-a-total"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Wide and long: pivoting rows to columns and back', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/pivoting-rows-to-columns (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Wide and long: pivoting rows to columns and back","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":3,"slug":"summaries-and-the-piles-behind-them","title":"Summaries, and the piles behind them"},"answer":null,"keyIdea":"Store and compute in long form and pivot only for display, because a query’s columns are fixed at plan time and cannot be discovered from the data, and a wide table needs a schema change for every new period.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"two-shapes-for-the-same-facts","runs":[{"text":"Two shapes for the same facts"}]},{"t":"p","runs":[{"text":"A revenue-by-city-by-month result can be long, with one row per city per month and three columns, or wide, with one row per city and a column for each month. Long is how a database stores and groups facts. Wide is how a human reads them across a page. Most reporting is the act of turning one into the other, and most of the friction comes from asking SQL to do the human-shaped half."}]},{"t":"h2","id":"long-to-wide-when-you-know-the-columns","runs":[{"text":"Long to wide, when you know the columns"}]},{"t":"p","runs":[{"text":"The tool is conditional aggregation, which the CASE lesson introduced: one aggregate per output column, each filtered to its slice."}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city,\n       sum(amount) FILTER (WHERE month = '2026-01-01') AS jan,\n       sum(amount) FILTER (WHERE month = '2026-02-01') AS feb,\n       sum(amount) FILTER (WHERE month = '2026-03-01') AS mar\nFROM monthly_sales\nGROUP BY city;"},{"t":"p","runs":[{"text":"Or "},{"text":"sum(CASE WHEN month = '2026-01-01' THEN amount END)","code":true},{"text":" where FILTER is unsupported. A city with no sales in February gets NULL in that column, and "},{"text":"coalesce(..., 0)","code":true},{"text":" decides whether the report shows a blank or a zero, which is a decision about meaning, not formatting."}]},{"t":"h2","id":"why-the-columns-must-be-typed-by-hand","runs":[{"text":"Why the columns must be typed by hand"}]},{"t":"p","runs":[{"text":"You will want to write “one column per month, whatever months exist”. SQL cannot do it, and the reason is structural rather than a missing feature. A query’s output columns are part of its type, fixed when the query is planned, before any row is read. The engine has to know there will be a column called "},{"text":"apr","code":true},{"text":" before it can decide how to produce one. Data-driven columns would mean the shape of the result depends on the result, which the planner cannot resolve."}]},{"t":"p","runs":[{"text":"The escape hatches confirm the rule. Postgres’s "},{"text":"crosstab()","code":true},{"text":" in the "},{"text":"tablefunc","code":true},{"text":" extension still needs the output column list declared in the call. DuckDB’s "},{"text":"PIVOT monthly_sales ON month USING sum(amount)","code":true},{"text":" discovers the months, and does it by running a first query to list them and then building the second query from that list; BigQuery’s "},{"text":"PIVOT","code":true},{"text":" requires the values in the statement. Where a report must pivot on values that change, the usual answer is to pivot in the reporting tool, or to generate the SQL from a query of the distinct values, which is what DuckDB does for you."}]},{"t":"h2","id":"wide-to-long","runs":[{"text":"Wide to long"}]},{"t":"p","runs":[{"text":"The reverse arrives whenever a spreadsheet lands in the database: "},{"text":"city, jan, feb, mar","code":true},{"text":" as four columns. Grouping by month is impossible in that shape. Unpivot it:"}]},{"t":"code","lang":"sql","label":"SQL","text":"SELECT city, month, amount\nFROM wide_sales\nCROSS JOIN LATERAL (VALUES ('2026-01-01', jan), ('2026-02-01', feb), ('2026-03-01', mar))\n  AS v(month, amount);"},{"t":"p","runs":[{"text":"Each source row becomes three rows, one per month. The portable version is a "},{"text":"UNION ALL","code":true},{"text":" of three "},{"text":"SELECT city, '2026-01-01', jan FROM wide_sales","code":true},{"text":" queries. DuckDB has "},{"text":"UNPIVOT wide_sales ON jan, feb, mar INTO NAME month VALUE amount","code":true},{"text":", which reads as what it does."}]},{"t":"h2","id":"why-wide-is-the-wrong-storage-shape","runs":[{"text":"Why wide is the wrong storage shape"}]},{"t":"p","runs":[{"text":"Store the wide table and every new month is an "},{"text":"ALTER TABLE","code":true},{"text":" to add a column, every query that groups by time has to be rewritten, and the table is mostly NULL for any city that started trading recently. Store long, and a new month is just more rows. The spreadsheet habit of one column per period is exactly the habit the first lesson of this course described as a spreadsheet stopping working, and the fix is the same: long in the table, wide only at the moment of display."}]},{"t":"h2","id":"the-free-path","runs":[{"text":"The free path"}],"role":"free"},{"t":"p","runs":[{"text":"A pivot table in LibreOffice Calc or Google Sheets performs this operation with a drag, and for a one-off report on a few thousand rows it is the right tool. Export the long result as CSV, open it, and pivot there. Keep the SQL that produced the long result, because the pivot is display and the query is the record of what the numbers mean."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Write the query long, check the numbers long, and pivot last, in the query only when the column list is fixed, otherwise in the tool that shows the result. A wide result whose columns you had to type is a report; a wide table somebody stores is a problem waiting for its thirteenth month."}]}],"check":{"question":[{"text":"A monthly pivot query uses one "},{"text":"sum(amount) FILTER (WHERE month = ...)","code":true},{"text":" per month. April’s sales are in the table, but the report has no April column. What is the direct cause?"}],"options":[{"text":[{"text":"FILTER ignores months that were absent when the table was created, so April needs a schema refresh before the expression can see it."}],"correct":false,"tempting":[{"text":"FILTER evaluates a condition per row at run time; nothing about the table’s creation date affects it."}]},{"text":[{"text":"The query names its output columns by hand, and nobody added an April expression, so the April rows are read and then discarded."}],"correct":true,"tempting":null},{"text":[{"text":"April’s rows are still NULL-typed because they arrived after the query was last planned."}],"correct":false,"tempting":[{"text":"Rows are not typed by arrival time, and re-planning happens on every execution."}]},{"text":[{"text":"GROUP BY city collapsed April into the March column because both fall in the same quarter."}],"correct":false,"tempting":[{"text":"Each FILTER tests an exact month; a quarter grouping would need to be written explicitly."}]}],"order":[0,3,1,2],"why":null},"prev":{"slug":"rollups-and-subtotals","title":"Subtotals and grand totals in one query: ROLLUP and GROUPING","minutes":8},"next":{"slug":"reconciling-a-total","title":"Reconciling: making a total tie out before you send it","minutes":8}}}