Pivoting SQL table with crosstab function

I made this query that lists all real estate prices per m2 per year in a city. It works nice yet all years end up in rows, with the prices behind them. I would prefer seeing the years in columns with the price in a cell. Via stackoverflow I found the crosstab function and have experimented with it. Unfortunately I can’t seem to make it work. Would love it if someone could have a look at the query.

Query output example

city        year    avg_price_m2
Amsterdam   2016    4407,51
Amsterdam   2017    5015,75
Amsterdam   2018    5648,1
Amsterdam   2019    5904,91

Desired

city        2016    2017      2018    2019
Amsterdam   4407,51 5015,75   5648,1  5904,91

Current query

SELECT city, 
       Extract(year FROM ondertekening_akte) AS year, 
       Round(Avg(transactieprijs_per_m2), 2) AS avg_price_m2 
FROM   transactiedata.transacties 
       JOIN bagactueel.gemeente 
         ON St_contains (bagactueel.gemeente.geovlak, 
            transactiedata.transacties.geopunt) 
WHERE  city = 'Amsterdam' 
       AND Extract(year FROM ondertekening_akte) > 2006 
GROUP  BY city, 
          year;

Pivot Attempt

select * from crosstab (
    $$select city,
    extract(year from ondertekening_akte) as year,
    ROUND(AVG(transactieprijs_per_m2),2) as avg_price_m2
    from transactiedata.transacties
    JOIN bagactueel.gemeente ON ST_Contains (bagactueel.gemeente.geovlak, transactiedata.transacties.geopunt)
    where city = 'Amsterdam'
    and extract(year from ondertekening_akte) > 2006
    group by city, year$$,

    $$select distinct extract(year from ondertekening_akte) as year from transactiedata.transacties order by year$$

)
AS (
    "city" text,
    "2007" int,
    "2008" int,
    "2009" int,
    "2010" int,
    "2011" int,
    "2012" int,
    "2013" int,
    "2014" int,
    "2015" int,
    "2016" int,
    "2017" int,
    "2018" int,
    "2019" int
)
;

I get this error:

ERROR:  invalid return type
DETAIL:  Query-specified return tuple has 14 columns but crosstab returns 17.

Answer

The second query in crosstab() returns more than 13 rows (exactly 16). You should probably add the condition:

select distinct extract(year from ondertekening_akte) as year 
from transactiedata.transacties 
where extract(year from ondertekening_akte) > 2006 --!!
order by year

Advertisement

Answer