Create a pivot table with PostgreSQL

Solution 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

Solution 2:

The best way to build pivot tables in Postgres are CASE expressions.

SELECT neighborhood,
       round(avg((CASE WHEN bedrooms = 0 THEN price END)), 2) AS "0",
       round(avg((CASE WHEN bedrooms = 1 THEN price END)), 2) AS "1",
       round(avg((CASE WHEN bedrooms = 2 THEN price END)), 2) AS "2",
       round(avg((CASE WHEN bedrooms = 3 THEN price END)), 2) AS "3"
FROM listings
GROUP BY neighborhood;

Running this on the question data yields

NEIGHBORHOOD                  0          1          2          3
-------------------- ---------- ---------- ---------- ----------
downtown                 256888     334000       NULL       NULL
riverview                  NULL     505000       NULL       NULL

Solution 3:

Another solution that implement with filter:

SELECT neighborhood,
   avg(price) FILTER (WHERE bedrooms = 0) AS "0",
   avg(price) FILTER (WHERE bedrooms = 1) AS "1",
   avg(price) FILTER (WHERE bedrooms = 2) AS "2",
   avg(price) FILTER (WHERE bedrooms = 3) AS "3"
FROM listings
GROUP BY neighborhood;