Tips on reshaping data, pivot-like?

Question

I have a table of levels and years that looks roughly like this:

INSERT INTO demo(first_name, rank, year) AS VALUES
  ('Peter',2,2017), ('Robert',3,2016), ('Evelyn',2,2017), 
  ('Rand',3,2017), ('Kofi',3,2017), ('Daniel',3,2016), ('Leonard',1,2017),
  ('Mary',1,2017), ('Frank',1,2017), ('John',3,2017), ('Mary',1,2016), 
  ('Gilbert',3,2016), ('Quince',2,2017), ('David',3,2017), ('Louis',2,2017),
  ('Felipe',2,2016), ('Alfonso',1,2017), ('Elise',3,2016), ('Jorge',3,2017),
  ('Mary',3,2016), ('Jasvant',2,2017), ('Paul',3,2017), ('Richard',2,2017),
  ('Christopher',2,2017), ('Johannes',2,2016), ('Nasser',3,2016), ('Bruce',3,2017), 
  ('John',3,2016), ('Frederic',3,2016);

I can capture totals for each level and year with a pretty basic COUNT():

SELECT  rank, COUNT(*), year
FROM demo
GROUP BY rank, year
ORDER BY year, rank;

That gives me a fairly tidy summary of the demo data:

Rank  Count  Year
1      1    2016
2      2    2016
3      8    2016
1      4    2017
2      7    2017
3      7    2017

My actual data is a lot bigger, and I'd like to be able to see it in a summary table that looks something more like this:

Rank    2016    2017
1         1     4
2         2     7
3         8     7

Is there a straightforward way to query a pivot-like view like this? For a much wider range of ranks and years?

Answer 1

You can pivot your data using crosstab, but as usual it requires a well-known column's name.

First you should install tablefunc module.

CREATE EXTENSION tablefunc;

Then you can call a crosstab with

SELECT * FROM 
  crosstab('SELECT rank, year::INT, COUNT(*)::INT ct FROM demo 
            GROUP BY 1, 2 ORDER BY 1, 2') 
  AS ct(rk INT, "2016" INT, "2017" INT)

rk | 2016 | 2017
-: | ---: | ---:
 1 |    1 |    4
 2 |    2 |    7
 3 |    8 |    7

dbfiddle here