SQL PIVOT: Rows to Columns for Data Engineer Interviews

PostgreSQL, MySQL, Redshift

PostgreSQL has no PIVOT keyword. The standard approach is conditional aggregation: wrap a CASE expression inside an aggregate function for each target column, then GROUP BY the row identifier. This pattern works on every SQL engine, which is why interviewers default to it.

SELECT
  product,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue ELSE 0 END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue ELSE 0 END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue ELSE 0 END) AS q3,
  SUM(CASE WHEN quarter = 'Q4' THEN revenue ELSE 0 END) AS q4
FROM sales
GROUP BY product;

PostgreSQL also provides a crosstab() function from the tablefunc extension. It's useful for production reports but rarely expected in interviews.

-- Enable the extension first
CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT * FROM crosstab(
  'SELECT product, quarter, revenue
   FROM sales ORDER BY 1, 2',
  $$VALUES ('Q1'), ('Q2'), ('Q3'), ('Q4')$$
) AS ct(product TEXT, q1 INT, q2 INT, q3 INT, q4 INT);

SQL Server, Oracle

SQL Server introduced PIVOT in SQL Server 2005. You specify the aggregate function, the column whose values become headers, and the list of values to pivot on. The syntax is concise but rigid: every pivot value must be listed explicitly. If a new quarter appears in the data, the query won't pick it up automatically.

SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM (
  SELECT product, quarter, revenue
  FROM sales
) AS src
PIVOT (
  SUM(revenue)
  FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pvt;

BigQuery

BigQuery added PIVOT support in 2022. The syntax is similar to SQL Server but uses BigQuery's function set. Before 2022, you had to use CASE WHEN. Many tutorials and interview prep materials still show the old approach, so know both.

SELECT *
FROM (
  SELECT product, quarter, revenue
  FROM sales
)
PIVOT (
  SUM(revenue)
  FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
);

Snowflake

Snowflake supports PIVOT and goes one step further: the IN clause accepts a subquery. This is the closest any major engine gets to true dynamic pivot in pure SQL, without building a query string at runtime.

-- Static pivot
SELECT *
FROM sales
PIVOT (
  SUM(revenue) FOR quarter IN ('Q1', 'Q2', 'Q3', 'Q4')
) AS p;

-- Dynamic pivot (Snowflake-only)
SELECT *
FROM sales
PIVOT (
  SUM(revenue)
  FOR quarter IN (SELECT DISTINCT quarter FROM sales)
) AS p;

Query the distinct values first. Build the CASE WHEN clauses as a string in Python, Java, or whatever runs your pipeline. Execute the generated query. This is how most production ETL handles it.

# Python example: dynamic pivot generation
import pandas as pd
from sqlalchemy import text

engine = create_engine(conn_string)

# Step 1: get distinct values
categories = pd.read_sql(
    "SELECT DISTINCT category FROM products ORDER BY 1",
    engine
)["category"].tolist()

# Step 2: build the pivot query
cases = ", ".join([
    f"SUM(CASE WHEN category = '{c}' "
    f"THEN amount ELSE 0 END) AS {c.lower().replace(' ', '_')}"
    for c in categories
])
query = f"SELECT region, {cases} FROM sales GROUP BY region"

# Step 3: execute
result = pd.read_sql(query, engine)

DECLARE @cols NVARCHAR(MAX);
DECLARE @sql  NVARCHAR(MAX);

-- Collect distinct values
SELECT @cols = STRING_AGG(QUOTENAME(quarter), ', ')
FROM (SELECT DISTINCT quarter FROM sales) AS q;

-- Build and execute the pivot query
SET @sql = N'
  SELECT product, ' + @cols + N'
  FROM (SELECT product, quarter, revenue FROM sales) AS src
  PIVOT (SUM(revenue) FOR quarter IN (' + @cols + N')) AS pvt';

EXEC sp_executesql @sql;

In dbt, you can call run_query() during compilation to fetch distinct values, then loop over them in Jinja to generate the CASE WHEN columns. The compiled SQL is static, but the template adapts to whatever's in the table.

{# dbt Jinja macro for dynamic pivot #}
{% set categories_query %}
  SELECT DISTINCT category FROM {{ ref('products') }}
{% endset %}
{% set categories = run_query(categories_query).columns[0].values() %}

SELECT
  region,
  {% for cat in categories %}
  SUM(CASE WHEN category = '{{ cat }}'
      THEN amount ELSE 0 END) AS {{ cat | lower | replace(' ', '_') }}
  {% if not loop.last %},{% endif %}
  {% endfor %}
FROM {{ ref('sales') }}
GROUP BY region

Return the data in its normalized form (key-value rows) and let the BI tool, Pandas, or Spark do the reshaping. Looker, Tableau, and Metabase all support column pivoting at render time. This keeps your SQL clean and the pipeline resilient to new values.

SUM(CASE WHEN region = 'US' THEN revenue END) returns NULL when no US rows exist for a group. Add ELSE 0 to get a zero instead. NULLs break downstream math and produce confusing report output.

-- Bad: returns NULL
SUM(CASE WHEN region = 'US'
    THEN revenue END)

-- Good: returns 0
SUM(CASE WHEN region = 'US'
    THEN revenue ELSE 0 END)

In SQL Server and Snowflake, any column in the PIVOT subquery that isn't the pivot column or the value column becomes an implicit GROUP BY key. If your inner query selects *, a timestamp or ID column forces one row per original row instead of one row per group.

-- Bad: extra columns = extra groups
SELECT * FROM sales
PIVOT (...) AS p;

-- Good: limit to needed columns
SELECT * FROM (
  SELECT product, quarter, revenue
  FROM sales
) AS src
PIVOT (...) AS p;

When you add SUM(CASE WHEN ...) columns, every non-aggregated column in SELECT must be in GROUP BY. Under pressure, candidates add the pivot columns but forget to update the GROUP BY clause. The error message is clear, but the fix costs you time and confidence.

PIVOT works in SQL Server, Snowflake, Oracle, and (since 2022) BigQuery. It does not exist in PostgreSQL, MySQL, SQLite, or Redshift. If the interviewer says 'PostgreSQL,' don't reach for PIVOT. Go straight to CASE WHEN. If they don't specify an engine, CASE WHEN is always the safe choice.