Home > Documentation > MySQL

Pivot Tables in MySQL

PIVOT operator is used for "orthogonal rotation" of table treating unique values of one column as multiple columns in the output result with with option to aggregate remaining column values. MySQL does not support this operator, however it provides another options to replace it. This article explores few techniques of implementation PIVOT in MySQL and its forks such as MariaDB, Percona.

Assume, there is MySQL table called "rental" that looks like this:

id    	city    	bedrooms    	price
1     	London        	0           	2580
2     	New York        1           	3340
3     	Singapore       1           	3850
4     	New York        0           	2340
5 	Paris           2		3560
6     	London        	0           	2140
7     	New York        1           	3600

Now we need a crosstab query that shows the average rental price per bedrooms as the columns and cities as the rows:

		0       1       2       
London    	2360  	-       -       
New York    	2340    3470  	-       
Paris		-	-	3560
Singapore	-	3850	-

The goal may be achieved by using aggregate function combined with MySQL conditional operator IF:

select city,
round(sum(if(bedrooms = 0, price, 0)) / 
	count(if(bedrooms = 0, id, NULL)),2) as "0",
round(sum(if(bedrooms = 1, price, 0)) / 
	count(if(bedrooms = 1, id, NULL)),2) as "1",
round(sum(if(bedrooms = 2, price, 0)) / 
	count(if(bedrooms = 2, id, NULL)),2) as "2",
round(sum(if(bedrooms = 3, price, 0)) / 
	count(if(bedrooms = 3, id, NULL)),2) as "3" 
from rental
group by city;

Have more questions? Contact us