Pivot table basics: rows to columns

from the Artful Common Queries page


From table tbl( class, member ), you want to cross-tabulate all classes with their members. In SQL terms, you aggregate members over classes. In MySQL:
SELECT class,GROUP_CONCAT(member)
FROM tbl
GROUP BY class;
With that simple query, you're halfway toward cross-tabulation, halfway to implementing a simple CUBE, and halfway to basic entity-attribute-value (EAV) logic. This is easier to see if we have two columns, rather than just one, to tabulate against the grouping column:
DROP TABLE IF EXISTS tbl;
CREATE TABLE tbl( id INT, colID INT, value CHAR(20) );
INSERT INTO tbl VALUES
  (1,1,'Sampo'),(1,2,'Kallinen'),(1,3,'Office Manager'),
  (2,1,'Jakko'),(2,2,'Salovaara'),(2,3,'Vice President');
To tabulate all colID and value values against all id values—that is, to write a reporting CUBE for the table—write a GROUP_CONCAT() instruction for each colID found in the table, then GROUP BY id:
SELECT 
  id, 
  GROUP_CONCAT( if(colID=1,value,NULL) ) AS 'First Name',
  GROUP_CONCAT( if(colID=2,value,NULL) ) AS 'Last Name',
  GROUP_CONCAT( if(colID=3,value,NULL) ) AS 'Job Title'
FROM tbl
GROUP BY id;
+------+------------+-----------+----------------+
| id   | First Name | Last Name | Title          |
+------+------------+-----------+----------------+
|    1 | Sampo      | Kallinen  | Office Manager |
|    2 | Jakko      | Salovaara | Vice President |
+------+------------+-----------+----------------+
Since Paul Spinks first taught a spreadsheet how to do that cross-tabulation trick in 1979, this has also been known as a pivot table: we pivot colID and value against ID.

(Of course for a proper EAV representation, we'd add an attributes table:

DROP TABLE IF EXISTS attrs;
CREATE TABLE attrs(colID INT,attr CHAR(12));
INSERT INTO attrs VALUES (1,'First Name'),(2,'Last Name'),(3,'Title');
and write a stored procedure to PREPARE the above query from table-based attribute names.)

Usually pivot queries need to do arithmetic, eg a table tracks communications room jobs and you need to report specific per-employee job counts. The query is just a series of sum( if(...) ) Select expressions wrapped in simple aggregation:

drop table jobs;
create table jobs( emp varchar(8), job varchar(8), qty int );
insert into jobs values
  ('sue','email',5),('bill','email',7),
  ('sally','print',2),('sue','sms',14),('bill','sms',2);

select 
  emp,
  sum( if(job='email',qty,0) ) as 'emails',
  sum( if(job='print',qty,0) ) as 'printings',
  sum( if(job='sms',  qty,0) )   as 'sms msgs'
from jobs
group by emp;
+-------+--------+-----------+----------+
| emp   | emails | printings | sms msgs |
+-------+--------+-----------+----------+
| bill  |      7 |         0 |        2 |
| sally |      0 |         2 |        0 |
| sue   |      5 |         0 |       14 |
+-------+--------+-----------+----------+
Another example, a simple sales table:
DROP TABLE IF EXISTS sales;
CREATE TABLE Sales (empID INT, yr SMALLINT, sales DECIMAL(10,2));
INSERT sales VALUES
(1 2005,12000),(1,2006,18000),(1,2007,25000),
(2,2005,15000),(2,2006,6000),(3,2006,20000),(3,2007,24000);
In Microsoft SQL Server, CUBE/PIVOT syntax for horizontal and vertical totals is:
SELECT EmpId, [2005], [2006], [2007], [ALL]
FROM (
  SELECT
    CASE WHEN GROUPING(EmpId)=0 THEN CAST(EmpId AS CHAR(7)) ELSE 'ALL' END AS EmpId,
    CASE WHEN GROUPING(Yr)=0 THEN CAST(Yr AS CHAR(7)) ELSE 'ALL' END AS Yr,
    SUM(Sales) AS Sales
  FROM Sales
  GROUP BY EmpId, Yr WITH CUBE
) AS s
PIVOT( SUM(Sales) FOR Yr IN ([2005], [2006], [2007], [ALL]) ) AS p
This can be done as simply as in the jobs example above, or as an emulation of SQL Server's CUBE syntax:

1. In an inner query, write one aggregating expression per reporting column,

2. In an outer query, build the horizontal sums:

SELECT 
  IFNULL(empId,'Totals') AS EmpId,       -- outer query labels rollup row
  sums.2005, sums.2006, sums.2007,       -- and calculates horizontal sums
  sums.2005 + sums.2006 + sums.2007 AS Sums
FROM (                                   -- inner query groups by employee
  SELECT                                 -- with an expression for each column
    EmpID,
    SUM( IF(Yr=2005,sales,0) ) As '2005',
    SUM( IF(Yr=2006,sales,0) ) As '2006',
    SUM( IF(Yr=2007,sales,0) ) As '2007'
  FROM Sales
  GROUP BY EmpID WITH ROLLUP
) AS sums;
+--------+----------+----------+----------+-----------+
| EmpId  | 2005     | 2006     | 2007     | Sums      |
+--------+----------+----------+----------+-----------+
| 1      | 12000.00 | 18000.00 | 25000.00 |  55000.00 |
| 2      | 15000.00 |  6000.00 |     0.00 |  21000.00 |
| 3      |     0.00 | 20000.00 | 24000.00 |  44000.00 |
| Totals | 27000.00 | 44000.00 | 49000.00 | 120000.00 |
+--------+----------+----------+----------+-----------+
That approach works beautifully for the common need to tabulate monthly amounts by year, say from an order history table orderhist(orderdate date,amount decimal(10,2)):
SELECT 
  IfNull(Year,'Totals') Year, 
  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`, 
  Qty AS Count, 
  Yrly as 'Yrly Total'
FROM (
  SELECT 
    year(orderdate) AS 'Year',
    Round(Sum(CASE WHEN Month(orderdate)= 1 THEN amount ELSE 0 END),2) AS Jan,
    Round(Sum(CASE WHEN Month(orderdate)= 2 THEN amount ELSE 0 END),2) AS Feb,
    Round(Sum(CASE WHEN Month(orderdate)= 3 THEN amount ELSE 0 END),2) AS Mar,
    Round(Sum(CASE WHEN Month(orderdate)= 4 THEN amount ELSE 0 END),2) AS Apr,
    Round(Sum(CASE WHEN Month(orderdate)= 5 THEN amount ELSE 0 END),2) AS May,
    Round(Sum(CASE WHEN Month(orderdate)= 6 THEN amount ELSE 0 END),2) AS Jun,
    Round(Sum(CASE WHEN Month(orderdate)= 7 THEN amount ELSE 0 END),2) AS Jul,
    Round(Sum(CASE WHEN Month(orderdate)= 8 THEN amount ELSE 0 END),2) AS Aug,
    Round(Sum(CASE WHEN Month(orderdate)= 9 THEN amount ELSE 0 END),2) AS Sep,
    Round(Sum(CASE WHEN Month(orderdate)=10 THEN amount ELSE 0 END),2) AS Oct,
    Round(Sum(CASE WHEN Month(orderdate)=11 THEN amount ELSE 0 END),2) AS Nov,
    Round(Sum(CASE WHEN Month(orderdate)=12 THEN amount ELSE 0 END),2) AS `Dec`,
    Count(*) AS Qty,
    Round(Sum(amount),2) AS Yrly
  FROM orderhist
  GROUP BY year WITH ROLLUP
) AS sums ;
More likely the required sums need to be calculated across joins. Build them in using a good text editor. Here's the above query for the orders and orderdetails table in the Northwind database:
SELECT 
  IfNull(Year,'Totals') Year, 
  Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, `Dec`, 
  quantity AS Count, 
  Yrly as 'Yrly Total'
FROM (
  SELECT 
    year(orderdate) AS 'Year',
    Round( Sum( If(Month(o.orderdate)= 1,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Jan,
    Round( Sum( If(Month(o.orderdate)= 2,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Feb,
    Round( Sum( If(Month(o.orderdate)= 3,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Mar,
    Round( Sum( If(Month(o.orderdate)= 4,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Apr,
    Round( Sum( If(Month(o.orderdate)= 5,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS May,
    Round( Sum( If(Month(o.orderdate)= 6,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Jun,
    Round( Sum( If(Month(o.orderdate)= 7,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Jul,
    Round( Sum( If(Month(o.orderdate)= 8,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Aug,
    Round( Sum( If(Month(o.orderdate)= 9,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Sep,
    Round( Sum( If(Month(o.orderdate)=10,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Oct,
    Round( Sum( If(Month(o.orderdate)=11,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS Nov,
    Round( Sum( If(Month(o.orderdate)=12,(d.unitprice*d.quantity)-d.discount,0) ), 2 ) AS `Dec`,
    Count(*) AS quantity,
    Round(Sum((unitprice*quantity)-discount),2) AS Yrly
  FROM orders o
  JOIN orderdetails d USING(orderID)
  GROUP BY year 
  WITH ROLLUP
) AS sums ; 
Nor is it hard to pivot against multiple Group By expressions, e.g., break out subtotals for NorthWind customer continent against yearly, quarterly and monthly orders ...
select 
  case 
    when y is null and q is null and m is null then 'TOTALS'
    else y 
  end as Year, 
  case 
    when y is null and q is null and m is null then ''
    when q is null and m is null then 'TOTAL'
    when m is null then concat('Q',q)
    else month
  end as Month, 
  `Africa`, `Asia`, `Europe`, `North America`, `South America`,
  total AS 'Total'
from (
  select 
    year(o.orderdate)                                 as y,
    quarter(o.orderdate)                              as q,
    month(o.orderdate)                                as m,
    monthname(o.orderdate)                            as month,   
    sum( if(continent='Africa',d.quantity,0) )        as 'Africa',
    sum( if(continent='Asia',d.quantity,0) )          as 'Asia',
    sum( if(continent='Europe',d.quantity,0) )        as 'Europe',
    sum( if(continent='North America',d.quantity,0) ) as 'North America',
    sum( if(continent='South America',d.quantity,0) ) as 'South America',
    sum( d.quantity )                                 as total 
  from 
    nwib.orders            o
    join nwib.orderdetails d on o.orderid=d.orderid
    join world.country     c on o.shipcountry=c.name
  where 
    c.continent in('Africa','Asia','Europe','North America','South America')
  group by 
    year(orderdate), quarter(orderdate), month(orderdate) with rollup
) x
order by      
  case 
    when y is null and q is null and m is null then 'TOTALS'
    else y
  end, 
  case         -- MYSQL LAG() NEEDS 8.0.12 OR LATER
    when q is null and m is null then 'TOTAL'
    when m is null then 10.1 + lag(m) over() 
    else 10+m
  end; 
+--------+-----------+--------+------+--------+---------------+---------------+-------+
| Year   | Month     | Africa | Asia | Europe | North America | South America | Total |
+--------+-----------+--------+------+--------+---------------+---------------+-------+
| 1996   | July      |      0 |    0 |   1053 |            11 |           289 |  1353 |
| 1996   | August    |      0 |    0 |    779 |            58 |           277 |  1114 |
| 1996   | September |      0 |    0 |    637 |            48 |            92 |   777 |
| 1996   | Q3        |      0 |    0 |   2469 |           117 |           658 |  3244 |
| 1996   | October   |      0 |    0 |   1055 |           246 |            75 |  1376 |
| 1996   | November  |      0 |    0 |   1095 |            40 |           124 |  1259 |
| 1996   | December  |      0 |    0 |   1107 |           123 |           353 |  1583 |
| 1996   | Q4        |      0 |    0 |   3257 |           409 |           552 |  4218 |
| 1996   | TOTAL     |      0 |    0 |   5726 |           526 |          1210 |  7462 |
| 1997   | January   |      0 |    0 |   1293 |           408 |           374 |  2075 |
| 1997   | February  |      0 |    0 |   1389 |            67 |           231 |  1687 |
| 1997   | March     |      0 |    0 |    734 |            61 |           454 |  1249 |
| 1997   | Q1        |      0 |    0 |   3416 |           536 |          1059 |  5011 |
| 1997   | April     |      0 |    0 |   1331 |           234 |           200 |  1765 |
| 1997   | May       |      0 |    0 |   1567 |            80 |           234 |  1881 |
| 1997   | June      |      0 |    0 |    750 |           252 |           145 |  1147 |
| 1997   | Q2        |      0 |    0 |   3648 |           566 |           579 |  4793 |
| 1997   | July      |      0 |    0 |    727 |           215 |           254 |  1196 |
| 1997   | August    |      0 |    0 |    931 |           215 |           589 |  1735 |
| 1997   | September |      0 |    0 |   1298 |           132 |           145 |  1575 |
| 1997   | Q3        |      0 |    0 |   2956 |           562 |           988 |  4506 |
| 1997   | October   |      0 |    0 |   1173 |            21 |           366 |  1560 |
| 1997   | November  |      0 |    0 |   1007 |           115 |           233 |  1355 |
| 1997   | December  |      0 |    0 |   1958 |             0 |           396 |  2354 |
| 1997   | Q4        |      0 |    0 |   4138 |           136 |           995 |  5269 |
| 1997   | TOTAL     |      0 |    0 |  14158 |          1800 |          3621 | 19579 |
| 1998   | January   |      0 |    0 |   1883 |           109 |           577 |  2569 |
| 1998   | February  |      0 |    0 |   1784 |            50 |           653 |  2487 |
| 1998   | March     |      0 |    0 |   2106 |           318 |           770 |  3194 |
| 1998   | Q1        |      0 |    0 |   5773 |           477 |          2000 |  8250 |
| 1998   | April     |      0 |    0 |   2632 |           156 |           570 |  3358 |
| 1998   | May       |      0 |    0 |    425 |            50 |           121 |   596 |
| 1998   | Q2        |      0 |    0 |   3057 |           206 |           691 |  3954 |
| 1998   | TOTAL     |      0 |    0 |   8830 |           683 |          2691 | 12204 |
| TOTALS |           |      0 |    0 |  28714 |          3009 |          7522 | 39245 |
+--------+-----------+--------+------+--------+---------------+---------------+-------+
With a shortish resultset, you might get away without an ORDER BY clause, but it's an ironclad SQL rule that with no ORDER BY clause, display order is arbitrary—so write it! Here, 10 is added to each month number so all month numbers have two digits to sort on, and an extra .1 is added in quarterly total rows to slide them in just after the previous monthly row.

With many columns or subtotals, pivot tables get complicated, time-consuming and error-prone. Automation is needed. Oracle and Microsoft SQL Server have CUBE syntax to simplify the job, especially for big pivot tables. MySQL doesn't. See "Automate pivot table queries" for how to roll your own cube with MySQL.

Last updated 21 May 2020




Return to the Artful Common Queries page