Dynamic Pivots With Unknown Columns

Every static pivot, whether CASE aggregation, SQL Server PIVOT, or Postgres crosstab, shares one limitation: you must list the output columns when you write the query.

But what if the categories are unknown, like product names that change weekly, or one column per active month? That is a dynamic pivot, and it is a senior-level interview question because plain SQL cannot return a result whose column list is decided at runtime.

SQL is statically typed at the result-set level: the planner must know the columns and their types before execution. A single query cannot say make one column for each value you happen to find.

So the universal technique is generate the SQL text in two steps: first query the distinct categories, then build a pivot query string from them and execute that string.