0PricingLogin▼
SQL Interview Prep · Lesson

Vendor PIVOT and Crosstab Syntax

SQL Server PIVOT and Postgres crosstab, and their limitations.

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;

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