How to Pivot a Table in SQL (MySQL, PostgreSQL, SQL Server)

Introduction

Pivoting transforms rows into columns — turning vertical data into a horizontal summary table. Every database handles this differently, so knowing the right syntax for your database is essential.

Pivot with CASE WHEN (Works in All Databases)

The universal approach that works in MySQL, PostgreSQL, SQL Server, SQLite, and Oracle. Use CASE inside aggregate functions.

-- Sample data: sales by quarter
-- | product | quarter | revenue |
-- | Widget  | Q1      | 1000    |
-- | Widget  | Q2      | 1500    |
-- | Gadget  | Q1      | 800     |

-- Pivot: quarters become columns
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;

Pivot in SQL Server (PIVOT Operator)

SQL Server has a native PIVOT operator that makes the syntax more declarative.

-- SQL Server PIVOT syntax:
SELECT product, [Q1], [Q2], [Q3], [Q4]
FROM (
  SELECT product, quarter, revenue
  FROM sales
) AS source
PIVOT (
  SUM(revenue)
  FOR quarter IN ([Q1], [Q2], [Q3], [Q4])
) AS pivot_table;

Pivot in PostgreSQL (crosstab)

PostgreSQL uses the crosstab() function from the tablefunc extension.

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

-- Crosstab pivot:
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);

Pivot with COUNT (Frequency Tables)

Pivoting counts instead of sums — useful for frequency analysis and cross-tabulation.

-- Count users by country and plan:
SELECT
  country,
  COUNT(CASE WHEN plan = 'free' THEN 1 END) AS free_users,
  COUNT(CASE WHEN plan = 'pro' THEN 1 END) AS pro_users,
  COUNT(CASE WHEN plan = 'enterprise' THEN 1 END) AS enterprise_users,
  COUNT(*) AS total
FROM users
GROUP BY country
ORDER BY total DESC;

Unpivot: Columns Back to Rows

The reverse of pivot — turn columns into rows using UNION ALL or UNPIVOT.

-- Universal unpivot with UNION ALL:
SELECT product, 'Q1' AS quarter, q1 AS revenue FROM sales_pivot
UNION ALL
SELECT product, 'Q2', q2 FROM sales_pivot
UNION ALL
SELECT product, 'Q3', q3 FROM sales_pivot
UNION ALL
SELECT product, 'Q4', q4 FROM sales_pivot;

-- SQL Server UNPIVOT:
SELECT product, quarter, revenue
FROM sales_pivot
UNPIVOT (revenue FOR quarter IN (Q1, Q2, Q3, Q4)) AS unpvt;

Best Practices

• Start with the CASE WHEN approach — it works everywhere
• Make sure your GROUP BY includes all non-aggregated columns
• For dynamic pivots (unknown column values), use dynamic SQL or application code
• Test with a small dataset first to verify the pivot logic
• Use aliases to make pivot column names readable

Generate SQL Queries with AI2SQL

Skip the syntax lookup. Describe what you need in plain English and AI2SQL generates the correct SQL query instantly.

Frequently Asked Questions