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 2024
