Translating SQL Server PIVOT to PostgreSQL with CASE WHEN

PIVOT in SQL Server transposes column values into fixed columns. In PostgreSQL the equivalent technique is conditional aggregation with CASE WHEN inside SUM/MAX/COUNT.

Before — PIVOT in SQL Server

SELECT *
FROM (SELECT region, quarter, revenue FROM billing) s
PIVOT (SUM(revenue) FOR quarter IN ([Q1],[Q2],[Q3])) p;

After — CASE WHEN in PostgreSQL

SELECT region,
  SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1,
  SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2,
  SUM(CASE WHEN quarter = 'Q3' THEN revenue END) AS q3
FROM billing
GROUP BY region;

Before — PIVOT in SQL Server

After — CASE WHEN in PostgreSQL

Analisador de Impacto