SQL Interview Prep · Lesson

Dynamic Pivots With Unknown Columns

Generating pivot columns when categories are not known in advance.

Dynamic Pivots With Unknown Columns is a free SQL Interview Prep lesson on CoddyKit — lesson 4 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the SQL Interview Prep learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

The Hard Pivot Question

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.

Why SQL Alone Cannot Do It

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.

Step 1: Collect the Categories

The first step is a normal query that lists the distinct values that will become columns. You typically order them for a stable column layout.

This result feeds the string-building step. In a real system you run this, capture the rows, and assemble the next query from them.

SELECT DISTINCT quarter
FROM sales
ORDER BY quarter;
-- e.g. Q1, Q2, Q3, Q4

Step 2: Build the Column List

Next, turn those values into a comma-separated list of CASE expressions (or bracketed names for PIVOT). Databases provide string-aggregation functions to do this in SQL itself.

In Postgres that is string_agg; in MySQL GROUP_CONCAT; in SQL Server STRING_AGG or the older FOR XML PATH trick.

-- Postgres: build the SELECT-list fragment
SELECT string_agg(
  format('SUM(CASE WHEN quarter = %L THEN amount END) AS %I',
         quarter, quarter),
  ', '
)
FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;

Step 3: Assemble and Execute

Concatenate the generated fragment into a full query string, then run it with dynamic execution: EXECUTE in PL/pgSQL, sp_executesql in SQL Server, or PREPARE/EXECUTE in MySQL.

This is the heart of a dynamic pivot: SQL writes SQL, then runs it.

-- SQL Server pattern
DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX);
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ',')
FROM (SELECT DISTINCT quarter FROM sales) q;
SET @sql = N'SELECT region, ' + @cols + '
  FROM (SELECT region, quarter, amount FROM sales) s
  PIVOT (SUM(amount) FOR quarter IN (' + @cols + ')) p;';
EXEC sp_executesql @sql;

PostgreSQL Full Example

In Postgres you wrap the three steps in a DO block or function. Build the column list with string_agg, splice it into the query, and run it with EXECUTE.

Because the result columns are unknown until runtime, a function returning this often uses RETURNS SETOF record or returns the rows as json, which the caller then expands.

DO $do$
DECLARE
  cols text;
  qry  text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN quarter=%L THEN amount END) AS %I', quarter, quarter), ', ')
  INTO cols
  FROM (SELECT DISTINCT quarter FROM sales ORDER BY 1) q;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

MySQL with Prepared Statements

MySQL has no pivot operator, so dynamic pivots build a conditional-aggregation string with GROUP_CONCAT, then run it via a prepared statement.

GROUP_CONCAT has a length limit (group_concat_max_len) that interviewers may mention, raise it if you have many categories.

SET @sql = NULL;
SELECT GROUP_CONCAT(DISTINCT
  CONCAT('SUM(CASE WHEN quarter=''', quarter,
         ''' THEN amount END) AS ', QUOTE(quarter))
) INTO @sql FROM sales;
SET @sql = CONCAT('SELECT region, ', @sql,
                  ' FROM sales GROUP BY region');
PREPARE st FROM @sql; EXECUTE st; DEALLOCATE PREPARE st;

The SQL Injection Risk

Because you are concatenating data values into executable SQL, dynamic pivots carry an injection risk. If a category value contains a quote or malicious text, it can break or hijack the generated query.

Always escape identifiers and literals with the engine's safe helpers: format('%I', ...) and %L in Postgres, QUOTENAME in SQL Server. Never paste raw values straight into the string.

-- Safe quoting prevents injection / breakage
-- Postgres: %I identifier, %L literal
format('SUM(CASE WHEN k=%L THEN v END) AS %I', cat, cat)
-- SQL Server: QUOTENAME(cat)

Returning Unknown Columns

A second hard part: the caller cannot know the result shape in advance. Common strategies interviewers accept:

Return the rows as JSON and let the application layer expand keys.
Have the procedure print or build the query, and run it as a second step.
Do the final pivot in application code (pandas, BI tool) once the categories are known.

There is no clean way to return arbitrary columns from one static call.

Worked Example: Pivot by Product

Suppose products come and go, and the report needs one revenue column per product currently in sales. You cannot hard-code the list, so you generate it. Postgres makes this readable: build the CASE fragment with string_agg and safe quoting, splice into a query, then EXECUTE.

Walk the interviewer through it: discover products, format each into a quoted column, assemble, run. The same shape applies in any engine; only the helpers change.

DO $do$
DECLARE cols text; qry text;
BEGIN
  SELECT string_agg(
    format('SUM(CASE WHEN product=%L THEN amount END) AS %I',
           product, product), ', ')
  INTO cols
  FROM (SELECT DISTINCT product FROM sales ORDER BY 1) p;
  qry := format('SELECT region, %s FROM sales GROUP BY region', cols);
  EXECUTE qry;
END $do$;

When to Avoid Dynamic Pivots

Strong candidates know when not to do this in SQL. Dynamic SQL is harder to read, test, secure, and cache. Often the better answer is:

Return long form from SQL and pivot in the application or reporting layer.
If the category set is small and slow-changing, use a static pivot and update it occasionally.

Reserve dynamic pivots for genuinely open-ended, ever-changing category sets.

Quick Check

Test the core reason dynamic pivots exist.

Recap

Dynamic pivots handle unknown column sets:

Static pivots fail because result columns must be fixed before execution.
Pattern: query distinct categories, build a pivot SQL string, execute it dynamically.
Use string_agg/GROUP_CONCAT/STRING_AGG to build the column list.
Escape values (%I/%L, QUOTENAME) to avoid SQL injection.
Often cleaner to return long form and pivot in the app layer.

Frequently asked questions

Is the “Dynamic Pivots With Unknown Columns” lesson free?

Yes — the full text of “Dynamic Pivots With Unknown Columns” is free to read here on the web, and the SQL Interview Prep course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the SQL Interview Prep course, upgrade to CoddyKit PRO.

What will I learn in “Dynamic Pivots With Unknown Columns”?

Generating pivot columns when categories are not known in advance. You practise SQL Interview Prep with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start SQL Interview Prep?

No prior experience is required. SQL Interview Prep on CoddyKit is structured for beginners through advanced learners; this is — lesson 4 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Dynamic Pivots With Unknown Columns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this SQL Interview Prep lesson?

Yes. Every SQL Interview Prep lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

← Back to SQL Interview Prep