Why must a portable SQL unpivot name its month columns when the query is written, and do vendor UNPIVOT operators remove that limit?
answer
- names resolved before rows are read
- column names are not values
- IN list on vendor operators
- new column: silent; renamed column: loud
- compare the list with the catalog
basics
~20 sSQL resolves every column name when the statement is analysed, and no portable syntax treats column names as data, so the list is written by hand. SQL Server's and Oracle's UNPIVOT operators still take an explicit column list.
solid answer
~50 sSQL resolves every column a statement reads, and the shape of its result, when the statement is analysed, before any row is read. A portable statement has no way to say "every column named like a month", and `SELECT *` does not help, because it expands to columns side by side and never turns a name into a value. So you write twelve `UNION ALL` branches or twelve `VALUES` rows. Vendor operators keep the limit: SQL Server and Oracle ship `PIVOT` and `UNPIVOT`, and their `UNPIVOT` takes an explicit column list, while PostgreSQL, MySQL and SQLite have no such operator. Add a thirteenth column and the query still runs, silently ignoring it, so totals drift low. Rename or drop a listed column and the query fails, which is the safer failure. So I guard the silent case by comparing the list with the catalog in a load test.
code
sql · 6 linesSELECT column_name
FROM information_schema.columns
WHERE table_name = 'sales_wide'
AND column_name NOT IN (
'product_id', 'jan_sales', 'feb_sales', 'mar_sales'
);go deeper
Remember that every unpivoted column is typed into the query by hand, and that a column added later is not picked up until someone edits the query.
Explain why: names are resolved during analysis, before rows exist, and a column name is not a value. Then name which engines have UNPIVOT and say that it still takes a list.
Separate the failure modes. A renamed column fails loudly, and an added column makes totals drift with no error. Show a concrete guard, such as a catalog comparison in the load.
A feed that adds a column each month is telling you something. Decide whether to change the feed's shape, or accept generated SQL and the review burden that comes with it.
## Why the list has to be fixed A SQL statement goes through **analysis** before it reads any data. The engine resolves each name to a column, works out each output column's type, and fixes the shape of the result. Only then does it run. Three things follow: - A column name is **an identifier, not a value**. An expression can choose among columns it already names, as `CASE` does, but it cannot compute a column's name from data or loop over a table's columns. - A `SELECT *` is expanded during analysis into a fixed list. It places columns side by side, and the name of each column stays out of the data. - The result's shape has to be known in advance, so the query text has to state which columns it reads. For an unpivot, that means the list is part of the statement. Twelve months mean twelve `UNION ALL` branches or twelve `VALUES` rows, and the author types each one. ```sql SELECT w.product_id, m.month_no, m.amount FROM sales_wide AS w CROSS JOIN LATERAL ( VALUES (1, w.jan_sales), (2, w.feb_sales), (3, w.mar_sales) ) AS m (month_no, amount); ``` ## Vendor operators by name Some engines have a dedicated keyword, and people often assume that it removes the list. On the two most common ones, it does not. | Engine | `PIVOT` / `UNPIVOT` operator | Column list needed | |---|---|---| | SQL Server | Yes | Yes, in the `IN` list | | Oracle | Yes | Yes, in the `IN` list | | PostgreSQL | No | Hand-written forms name each column | | MySQL | No | Hand-written forms name each column | | SQLite | No | Hand-written forms name each column | These operators are a shorter way to write the same thing, with their own NULL behaviour and type rules. Their full syntax belongs to each vendor's dialect. A few analytical engines do accept a column pattern in their own `UNPIVOT`, so check yours before you generalise. That convenience is not portable either. ## What happens when the source changes The source of an unpivot is often a feed you do not control. The query's failure depends on the kind of change: 1. **A column is added.** The statement still analyses and runs, because it never mentioned the new column. Its values are simply absent, and totals drift low with no error. 2. **A listed column is renamed or dropped.** The name no longer resolves, so the statement fails at analysis, and you hear about it at once. 3. **A listed column changes type.** This depends on the engine and on the other columns, from an immediate error to a quiet change in the output type. The added column is the dangerous one, and it is the likely one, since a spreadsheet that gains a column for each new month has this exact shape. ## Living with a fixed list There are honest ways to handle this short of generating SQL: - **Guard it with a test.** Compare the column list against the catalog during the load, and fail when a column that nobody has classified appears. - **Reconcile totals.** Check that source and output totals match per month, which catches a missing column only if the check reads every source column. - **Change the feed.** If a new column arrives each month, then the wide shape is the real problem. Ask for long rows at the source, or load each period into a staging table with a fixed shape. - **Keep the form easy to edit.** A `VALUES` list makes adding a month a one-line change. ## What lifts the limit, and at what cost Two routes exist, and neither is portable SQL: - **Generating the statement at run time** from the catalog, using dynamic SQL. This solves the list and adds its own hazards around quoting identifiers. It is a separate subject from the unpivot itself. - **Converting the row to a document.** On PostgreSQL, `jsonb_each_text(to_jsonb(w))` returns one key-and-value row per column, without naming any of them. Every value comes back as text, the key is whatever the column is called, and the query still has to exclude identifying columns. It is useful for exploring a table, and weak as a load step, because any new column flows through unchecked. So "must" is accurate for portable statements, and the escape routes are engine features that trade away the checking a fixed list gives you.
- A monthly total from a working unpivot starts running below the source spreadsheet's total, with no errors. What do you check first?Whether the source gained a column the query does not list, since an added column never causes an error. I compare the table's current columns in the catalog with the unpivot's list, then compare per-column totals against per-label totals. A mismatch there points to a mislabelled branch instead. If the figures reconcile, I look at filters and at NULLs.
- Do SQL Server's or Oracle's UNPIVOT operators treat the listed columns differently from UNION ALL in any other way?Yes. Both leave NULL cells out by default, and on SQL Server there is no option to keep them, whereas the hand-written forms return them as rows. They are also strict about types: SQL Server requires the listed columns to share one exact type, where `UNION ALL` would convert by its own rules. So a swap can change results without any change in the column list.
- SELECT * in a branch unpivots all columns, so new months are picked up automatically.
- A new source column makes a fixed unpivot fail, so you will always hear about it.
- An UNPIVOT keyword reads the table's columns itself, so SQL Server's needs no column list.
- PostgreSQL ships an UNPIVOT operator, the same as SQL Server does.
- A portable expression can compute a column name from a row's data and read that column.