3

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?

2
6

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

2
  • Getting quicker on those answers, fingers on fire today. – I Support The Boycott Mar 21 '18 at 21:32
  • 2
    I also had to do CREATE EXTENSION tablefunc; as a privileged user. – Amanda Mar 21 '18 at 21:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.