Pivoting SQL table with crosstab function

Question

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.

Don't do this in SQL, this is not what it was intended for. Do the transposing of rows to columns in your application when you display the data. — , Nov 13 '19 at 20:55

score 0 · Accepted Answer · answered Nov 13 '19 at 20:57

0

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

answered Nov 13 '19 at 20:57

klin

112,967
15
204
232

This did the trick! Thanks. Easiest way with what I had. "2007" INT had to be NUMERIC I found out as well. – Edwin Hans Nov 14 '19 at 19:21

score 0 · Answer 2 · answered Nov 13 '19 at 20:58

SQL isn't designed for things like that, but if I really have to do it, I typically prefer conditional aggregation rather than trying to fight the crosstab() function:

select city,
       ROUND(AVG(transactieprijs_per_m2),2) filter (where extract(year from ondertekening_akte) = 2007) as "2007",
       ROUND(AVG(transactieprijs_per_m2),2) filter (where extract(year from ondertekening_akte) = 2008) as "2008",
       ROUND(AVG(transactieprijs_per_m2),2) filter (where extract(year from ondertekening_akte) = 2009) as "2009",
       ROUND(AVG(transactieprijs_per_m2),2) filter (where extract(year from ondertekening_akte) = 2010) as "2010",
       ... and so on ...
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;

score -2 · Answer 3 · answered Nov 13 '19 at 20:53

I know how to PIVOT in SQL Server, but I'm pretty sure Crosstab and MSSQL share same syntax for PIVOT. Since I don't have any ways to test it, can you try this code?

    select City, [2011],[2012],[2013],[2014]
    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
PIVOT(ROUND(AVG(transactieprijs_per_m2),2)
     FOR YEAR IN ([2011],[2012],[2013],[2014])

Pivoting SQL table with crosstab function

3 Answers3