Rotates rows into columns, creating cross-tab reports.
Rotates rows into columns, turning distinct values from one column into separate output columns. Essential for creating crosstab reports from normalized data. SQL Server has native PIVOT; PostgreSQL uses crosstab() from tablefunc; MySQL uses MAX with CASE.
Compatibility
| Database System | Support Status | Since Version | Notes |
|---|---|---|---|
| MySQL | Not Supported | - | No PIVOT—use conditional aggregation |
| PostgreSQL | Not Supported | - | Use tablefunc crosstab() |
| SQL Server | Native | 2005 | Since SQL Server 2005 |
| Oracle | Native | 11g | Since Oracle 11g |
| SQLite | Not Supported | - | No PIVOT—use CASE/ GROUP BY |
Details
Shorthand for conditional aggregation—transform rows into columns.
Standard Syntax
SELECT *
FROM (
SELECT category, month, value
FROM sales
) AS src
PIVOT (
SUM(value) FOR month IN ([Jan],[Feb],[Mar])
) AS p;
Version Support
MySQL: Not supported
PostgreSQL: Not supported
SQL Server: Native since 2005
Oracle: Native since 11g
SQLite: Not supported
Per-Database Syntax & Notes
MySQL Alternative syntax
No PIVOT—use conditional aggregation
SELECT
category,
SUM(CASE WHEN month='Jan' THEN value END) AS Jan,
…
FROM sales
GROUP BY category;
PostgreSQL Alternative syntax
Use tablefunc crosstab()
SELECT *
FROM crosstab(
'SELECT category,month,value FROM sales ORDER BY 1,2',
'VALUES (''Jan''),( ''Feb''),( ''Mar'')'
) AS ct(category text, Jan int, Feb int, Mar int);
SQL Server Native syntax
Since SQL Server 2005
SELECT *
FROM (
SELECT category, month, value FROM sales
) AS src
PIVOT(
SUM(value) FOR month IN ([Jan],[Feb],[Mar])
) AS p;
Oracle Native syntax
Since Oracle 11g
SELECT *
FROM (
SELECT category, month, value FROM sales
)
PIVOT(
SUM(value) FOR month IN ('Jan' AS Jan,'Feb' AS Feb,'Mar' AS Mar)
);
SQLite Alternative syntax
No PIVOT—use CASE/ GROUP BY
SELECT
category,
SUM(CASE WHEN month='Jan' THEN value END) AS Jan,
…
FROM sales
GROUP BY category;