Pivot Tables in SQL

-- Original data (rows):
-- dept    | month | sales
-- Sales   | Jan   | 1000
-- Sales   | Feb   | 1500
-- HR      | Jan   | 800

-- Pivoted (columns):
SELECT department,
  SUM(CASE WHEN month = 'Jan' THEN sales ELSE 0 END) AS Jan,
  SUM(CASE WHEN month = 'Feb' THEN sales ELSE 0 END) AS Feb,
  SUM(CASE WHEN month = 'Mar' THEN sales ELSE 0 END) AS Mar
FROM sales
GROUP BY department;

Pivoting converts row values into columns. The CASE WHEN inside SUM pattern is the standard way to do this in MySQL and PostgreSQL.

SELECT department,
  COUNT(CASE WHEN gender = 'Male' THEN 1 END) AS male,
  COUNT(CASE WHEN gender = 'Female' THEN 1 END) AS female
FROM employees
GROUP BY department;

COUNT(CASE WHEN ... THEN 1 END) is equivalent to SUM(CASE WHEN ... THEN 1 ELSE 0 END). Both work. COUNT version is slightly cleaner.

SELECT product_name,
  SUM(CASE WHEN QUARTER(order_date) = 1 THEN revenue ELSE 0 END) AS Q1,
  SUM(CASE WHEN QUARTER(order_date) = 2 THEN revenue ELSE 0 END) AS Q2,
  SUM(CASE WHEN QUARTER(order_date) = 3 THEN revenue ELSE 0 END) AS Q3,
  SUM(CASE WHEN QUARTER(order_date) = 4 THEN revenue ELSE 0 END) AS Q4
FROM sales
GROUP BY product_name;

QUARTER() returns 1-4. This quarterly breakdown is a standard MIS report format used in Excel and Power BI — knowing how to produce it in SQL is very useful.

-- SQL Server PIVOT:
SELECT department, [Jan], [Feb], [Mar]
FROM (
  SELECT department, month, sales FROM sales
) src
PIVOT (
  SUM(sales) FOR month IN ([Jan], [Feb], [Mar])
) pvt;

-- This is cleaner than CASE WHEN but only works in SQL Server.
-- MySQL and PostgreSQL require the CASE WHEN approach.

SQL Server and Oracle have native PIVOT syntax. MySQL and PostgreSQL do not. For interviews unless specifically asked about SQL Server, stick with the CASE WHEN method.