sql pivot with dynamic columns

TL;DR

SQL has no universal dynamic pivot: if you know the columns in advance, use static CASE WHEN or the PIVOT clause. If the columns depend on the data, generate the pivot SQL from a query over the distinct values, or use your database's crosstab function (Postgres tablefunc). The columns have to come from somewhere, so either you list them or the database reads them first.

The query

sql pivot with dynamic columns

Use this when

• Pivot output columns change as data changes, e.g. one column per product, month, or tag
• You have tried static PIVOT and a new value silently drops out of the report
• You are deciding between CASE WHEN, crosstab, and dynamic SQL for a pivoting query

Not for

• Fixed column sets, where a static CASE WHEN pivot is simpler and faster to review
• Pivoting done in pandas or a BI tool, where the dataframe or tool handles it natively
• Transposing truly unknown-shaped data at huge scale, which usually means the model wants rethinking

Steps

1. Confirm the problem is real: list the distinct pivot values and check they change over time:

SELECT DISTINCT status FROM orders ORDER BY status;

Expected output: the current value list. Run it again next month; if a new value appears, a static pivot misses it. That is the trigger for a dynamic approach.

1. For a known-small value set, start with static CASE WHEN. It is the portable baseline every database understands:

SELECT region,
  SUM(CASE WHEN status = 'shipped' THEN amount ELSE 0 END) AS shipped,
  SUM(CASE WHEN status = 'pending' THEN amount ELSE 0 END) AS pending,
  SUM(CASE WHEN status = 'cancelled' THEN amount ELSE 0 END) AS cancelled
FROM orders
GROUP BY region;

Expected output: one row per region with a column per status. Works everywhere, but adding a new status means editing the query.

1. For unknown value sets in Postgres, use the tablefunc crosstab. It reads the distinct values as data instead of hardcoding them:

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM crosstab(
  'SELECT region, status, SUM(amount) FROM orders GROUP BY 1,2 ORDER BY 1,2',
  'SELECT DISTINCT status FROM orders ORDER BY 1'
) AS ct(region text, shipped numeric, pending numeric, cancelled numeric);

Expected output: a pivoted grid. The catch: you still declare the output column list, so this helps with the query text, not the declared shape. Fine for dashboards, not for a self-describing result.

1. For a fully dynamic result, generate the pivot SQL from the value list. Query the distinct values, build the CASE WHEN text, and run it:

SELECT string_agg(
  'SUM(CASE WHEN status = ' || quote_literal(status) || ' THEN amount ELSE 0 END) AS ' || quote_ident(status),
  ', ' ORDER BY status
)
FROM (SELECT DISTINCT status FROM orders) s;

Expected output: a SQL string you can execute. In Postgres wrap it in a DO block or a plpgsql function that RETURNS query EXECUTE; in SQL Server use sp_executesql; in Snowflake use a stored procedure. Same pattern everywhere, different plumbing.

1. If you are in Snowflake or BigQuery, use the native PIVOT with a subquery for the value list where supported, and accept its limits:

SELECT * FROM orders
PIVOT(SUM(amount) FOR status IN (SELECT DISTINCT status FROM orders));

Expected output: pivoted columns without hand-listing. Snowflake supports this shape; other databases may not, so check your version before relying on it.

1. Cap the column count. A dynamic pivot over a high-cardinality column (user ids, timestamps) produces a result set no one can read and some drivers choke on:

SELECT status FROM orders GROUP BY status ORDER BY COUNT(*) DESC LIMIT 20;

Expected output: the top 20 values. Pivot on those and bucket the rest into an 'other' column. A pivot with 500 columns is a bug, not a feature.

Variant phrasings

dynamic pivot sql server without knowing columns

Build the column list with STRINGAGG and run it through spexecutesql. The PIVOT clause itself still needs the list at parse time, so generation is the only route.

postgres pivot rows to columns dynamic

crosstab from tablefunc for the common case, or plpgsql dynamic SQL when the output shape must be fully data-driven. Note that functions returning dynamic shapes need a defined return type at call time.

pivot with unknown number of columns mysql

GROUP_CONCAT to build the CASE WHEN string, then PREPARE and EXECUTE. Same generate-then-run pattern as everywhere else.

Why it matters

Dynamic pivots are where SQL's static typing collides with reporting reality. The failure mode is silent: a new product, status, or month appears in the data and the static pivot drops it, so the report looks fine and is wrong. The dynamic approaches trade a bit of query-planning convenience for correctness, and the column-count cap in step 6 keeps them from becoming a different kind of wrong.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_59WuBxykz1ZdB-XAVjRtyA