Transforming rows into columns and back

Turning rows into columns (and vice versa) is a common reporting requirement. Before Oracle 11g, you had to use DECODE or CASE with GROUP BY. Now PIVOT and UNPIVOT make it much cleaner.

Recipe #1 - PIVOT rows into columns

Given a table of sales by quarter, turn each quarter into its own column:

SELECT *
FROM (
  SELECT salesman, quarter, amount
  FROM sales
)
PIVOT (
  SUM(amount)
  FOR quarter IN ('Q1' AS q1, 'Q2' AS q2, 'Q3' AS q3, 'Q4' AS q4)
)
ORDER BY salesman;

This transforms data from:

Into:

Recipe #2 - PIVOT with multiple aggregations

You can pivot with multiple aggregate functions:

SELECT *
FROM (
  SELECT deptno, job, sal
  FROM emp
)
PIVOT (
  COUNT(*) AS cnt,
  AVG(sal) AS avg_sal
  FOR job IN ('CLERK' AS clerk, 'MANAGER' AS mgr, 'ANALYST' AS analyst)
);

Recipe #3 - UNPIVOT columns into rows

UNPIVOT is the reverse — it turns columns back into rows:

SELECT *
FROM quarterly_sales
UNPIVOT (
  amount FOR quarter IN (q1 AS 'Q1', q2 AS 'Q2', q3 AS 'Q3', q4 AS 'Q4')
)
ORDER BY salesman, quarter;

Recipe #4 - Pre-11g PIVOT with DECODE

If you're on an older Oracle version, use DECODE with GROUP BY:

SELECT salesman,
       SUM(DECODE(quarter, 'Q1', amount, 0)) AS q1,
       SUM(DECODE(quarter, 'Q2', amount, 0)) AS q2,
       SUM(DECODE(quarter, 'Q3', amount, 0)) AS q3,
       SUM(DECODE(quarter, 'Q4', amount, 0)) AS q4
FROM sales
GROUP BY salesman;