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           256888 2     downtown        1           334000 3     riverview       1           505000 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 look something like this (numbers are made up, columns are the bedrooms):

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

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 as instructed in great detail in this related answer:

• PostgreSQL Crosstab Query