0Pricing
SQL Interview Prep · Lesson

Vendor PIVOT and Crosstab Syntax

SQL Server PIVOT and Postgres crosstab, and their limitations.

Vendor PIVOT and Crosstab Syntax is a free SQL Interview Prep lesson on CoddyKit — lesson 2 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.

Beyond Conditional Aggregation

You already know the portable CASE pivot. But interviewers also want to know whether you can use vendor-specific pivot operators when they are available.

SQL Server ships a dedicated PIVOT operator. PostgreSQL offers a crosstab function in the tablefunc extension. Knowing both, and their sharp edges, signals real-world experience.

SQL Server PIVOT Anatomy

SQL Server's PIVOT takes three things:

  • An aggregate over the value column.
  • A FOR clause naming the column whose values become new columns.
  • An IN list of the literal values to turn into columns.

It must be applied to a derived table that exposes exactly the key, the spreading column, and the value, nothing more.

SELECT region, [Q1], [Q2]
FROM (SELECT region, quarter, amount FROM sales) AS src
PIVOT (
  SUM(amount)
  FOR quarter IN ([Q1], [Q2])
) AS p;

The Implicit GROUP BY

A subtle PIVOT gotcha interviewers test: the grouping is implicit. SQL Server groups by every column in the source that is NOT the aggregated column or the FOR column.

So if your derived table accidentally includes an extra column like order_id, the pivot groups by it too and you get far more rows than expected. Always trim the inner query to just key, spread, and value.

-- WRONG: order_id leaks in and breaks grouping
FROM (SELECT region, quarter, amount, order_id FROM sales) AS src
PIVOT (SUM(amount) FOR quarter IN ([Q1],[Q2])) AS p;
-- The pivot now groups by region AND order_id

Bracketed Column Names

In SQL Server the pivoted column names are the literal values from the data, wrapped in square brackets. If a value starts with a digit or contains spaces, brackets are mandatory.

You select them by the same bracketed name in the outer SELECT. This is also why PIVOT cannot handle unknown values without dynamic SQL: the IN list is hard-coded.

SELECT region, [2023], [2024]
FROM (SELECT region, yr, amount FROM sales) AS s
PIVOT (SUM(amount) FOR yr IN ([2023], [2024])) AS p;

PostgreSQL crosstab

PostgreSQL has no PIVOT keyword. Instead, the tablefunc extension provides crosstab, a function that takes a SQL string and reshapes its output.

You must enable the extension first. crosstab expects the source query to return exactly three columns: row identifier, category, and value, in that order.

CREATE EXTENSION IF NOT EXISTS tablefunc;

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);

The Column Definition List

The most error-prone part of crosstab is the trailing AS ct(...) column definition list. You must declare the output column names and types yourself, and they must match the number and order of categories.

If a category is missing for a row, crosstab fills it positionally, which can misalign data unless you use the two-argument form below.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2'
) AS ct(region text, q1 numeric, q2 numeric);
-- ct(...) MUST list every output column and its type

Two-Argument crosstab

To avoid misalignment when some rows lack some categories, use the two-argument form. The second query returns the full, ordered list of category values, so crosstab knows exactly which column each value belongs in.

This is the robust form interviewers expect when categories are sparse.

SELECT *
FROM crosstab(
  'SELECT region, quarter, amount FROM sales ORDER BY 1, 2',
  'SELECT DISTINCT quarter FROM sales ORDER BY 1'
) AS ct(region text, q1 numeric, q2 numeric);

MySQL Has Neither

If the interviewer asks about MySQL, the answer is direct: MySQL has no PIVOT and no crosstab. Your only option there is conditional aggregation with CASE (or the SUM(... ) + IF() shorthand).

This is exactly why the portable CASE pattern is so valued: it is the lowest common denominator that works everywhere.

-- MySQL: only conditional aggregation works
SELECT
  region,
  SUM(IF(quarter = 'Q1', amount, 0)) AS q1,
  SUM(IF(quarter = 'Q2', amount, 0)) AS q2
FROM sales
GROUP BY region;

Worked Example: Status Counts in SQL Server

A reporting ask: "one row per region, with a column counting orders in each status." In SQL Server, feed a trimmed derived table into PIVOT using COUNT.

Because you count the status column itself, every non-NULL status row in a bucket is tallied. The outer SELECT lists each status as a bracketed column. This is the concise alternative to writing three COUNT(CASE ...) expressions.

SELECT region, [pending], [shipped], [delivered]
FROM (SELECT region, status FROM orders) AS src
PIVOT (
  COUNT(status)
  FOR status IN ([pending], [shipped], [delivered])
) AS p;

Shared Limitations

Both PIVOT and crosstab share the same core limitation as conditional aggregation: the output columns must be known when you write the query.

  • SQL Server: the IN list is literal.
  • Postgres crosstab: the column definition list is literal.

Neither can discover categories at runtime. That requires building the SQL string dynamically.

Which Should You Use?

A good interview answer compares them honestly:

  • CASE aggregation: portable, readable, works in every engine. Default choice.
  • SQL Server PIVOT: concise for many columns, but the implicit grouping surprises people.
  • Postgres crosstab: powerful but verbose, needs an extension and a column definition list.

When in doubt, reach for conditional aggregation and mention the vendor operators as alternatives.

Quick Check

Pin down the SQL Server PIVOT behavior interviewers probe.

Recap

Vendor pivot syntax in one screen:

  • SQL Server: PIVOT (SUM(x) FOR col IN ([a],[b])), with an implicit GROUP BY over leftover columns.
  • Postgres: crosstab() from tablefunc, needing a column definition list; use the two-argument form for sparse data.
  • MySQL: neither exists, use CASE.
  • All three need columns known at write time.

Frequently asked questions

Is the “Vendor PIVOT and Crosstab Syntax” lesson free?

Yes — the full text of “Vendor PIVOT and Crosstab Syntax” 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 “Vendor PIVOT and Crosstab Syntax”?

SQL Server PIVOT and Postgres crosstab, and their limitations. 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 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Vendor PIVOT and Crosstab Syntax” 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

  1. Pivoting With Conditional Aggregation
  2. Vendor PIVOT and Crosstab Syntax
  3. Unpivoting Columns Into Rows
  4. Dynamic Pivots With Unknown Columns
← Back to SQL Interview Prep