Pivot tables in PostgreSQL

PIVOT operator is used to "rotate" table treating unique values of one column as multiple columns in the output result with capability of aggregation on remaining column values. PostgreSQL does not support this operator, however it provides another options to replace it. This article explores few techniques of implementation PIVOT in PostgreSQL.

Assume, there is PostgreSQL table called "pricerent" 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	-

First approach to this task is CASE statement:

select city,
round(avg(case when bedrooms = 0 then price else 0 end)::numeric,2) as "0",
round(avg(case when bedrooms = 1 then price else 0 end)::numeric,2) as "1",
round(avg(case when bedrooms = 2 then price else 0 end)::numeric,2) as "2",
round(avg(case when bedrooms = 3 then price else 0 end)::numeric,2) as "3" 
from pricerent
group by city;

Second option is crosstab function from PostgreSQL tablefunc extension. So, do not forget to create it:

CREATE EXTENSION IF NOT EXISTS tablefunc;

For the task specified above we need to get average values first:

SELECT city, bedrooms, avg(price)
FROM   pricerent
GROUP  BY 1,2
ORDER  BY 1,2

Now pass the results of that query to crosstab:

SELECT * 
FROM crosstab(
	'SELECT city, bedrooms, avg(price) 
	FROM   pricerent 
	GROUP  BY 1,2 
	ORDER  BY 1,2' 
) AS ct(city text, avgprice_0 real, avgprice_1 real, avgprice_2 real);

Have questions? Contact us