Create pivot/wide table using postgresql return error

Galih 0
Viewed 29

I have a table that look like this

rank country year
1 USA 2020
2 Austria 2020
3 China 2022
1 France 2021
2 Canada 2021
3 USA 2021

I want to make it into a wide table with the result as follows

rank 2020 2021
1 USA France
2 Austria Canada
3 China USA

I tried to use CROSSTAB function from various resources on the internet with the code as follows:

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB (
'SELECT rank, country, year
FROM country_yearly') AS ct(rank int, 2020 varchar, 2021 varchar);

It doesn't work and gives me this message

ERROR: syntax error at or near "2020"

pivot-table sql postgresql
1 Answers

In crosstab, you need to user order by , and give the year column in double quotes

For filtering the year i used generate series.

CREATE EXTENSION IF NOT EXISTS tablefunc;
SELECT * FROM CROSSTAB (
'SELECT rank,  year,country
FROM conyear order by 1,2' ,'SELECT g FROM generate_series(2020,2021) g') 
  AS ct(rank int, "2020" varchar, "2021" varchar);

CREATE TABLE conyear 
(
    rank    int,
    country     varchar(300),
    year    int
);

INSERT INTO conyear (rank ,country ,year) VALUES ('1', 'USA', '2020');
INSERT INTO conyear (rank ,country ,year) VALUES ('2', 'Austria', '2020');
INSERT INTO conyear (rank ,country ,year) VALUES ('3', 'China', '2022');
INSERT INTO conyear (rank ,country ,year) VALUES ('1', 'France', '2021');
INSERT INTO conyear (rank ,country ,year) VALUES ('2', 'Canada', '2021');
INSERT INTO conyear (rank ,country ,year) VALUES ('3', 'USA', '2021');

The output ,(in you output you posted china is not present in the year 2020)

rank    2020    2021
1   USA     France
2   Austria     Canada
3   null    USA
Sund'er0
Related