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?

• soandos: have you looked at the crosstab function in postgres?
• Amanda: I have not. Sounds like I should. stackoverflow.com/questions/3002499/postgresql-crosstab-query seems like it documents it well.

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

• Evan Carroll: Getting quicker on those answers, fingers on fire today.
• Amanda: I also had to do CREATE EXTENSION tablefunc; as a privileged user.