Create a pivot table with PostgreSQL

Question

Suppose I have a table in Postgres called listings that looks like this:

id	neighborhood	bedrooms	price
1	downtown	0	189000
2	downtown	3	450000
3	riverview	1	300000
4	riverview	0	250000
5	downtown	1	325000
6	riverview	2	350000

etc.

How do I write a crosstab query that shows the average price per bedrooms as the columns and neighborhoods as the rows?

The output of the query should have the following format:

neighborhood	0	1	2	3
downtown	189000	325000	-	450000
riverview	250000	300000	350000	-

etc.

Answer 1

First compute the average with the aggregate function avg():

SELECT neighborhood, bedrooms, avg(price)
FROM   listings
GROUP  BY 1,2
ORDER  BY 1,2;

Then feed the result to the crosstab() function (provided by the additional module tablefunc). Cast the avg to int if you want rounded results as displayed:

SELECT *
FROM   crosstab(
   'SELECT neighborhood, bedrooms, avg(price)::int
    FROM   listings
    GROUP  BY 1, 2
    ORDER  BY 1, 2;'

  , $$SELECT unnest('{0,1,2,3}'::int[])$$
   ) AS ct ("neighborhood" text, "0" int, "1" int, "2" int, "3" int);

fiddle

Detailed instructions:

• PostgreSQL Crosstab Query

The same can be achieved with the aggregate FILTER clause. A bit simpler, and doesn't need an additional module, but typically slower. Related answer with side-by-side solutions:

• Conditional SQL count