Some help with a crosstab query in postgresql

Question

I have a data set that looks like this:

CREATE TABLE game
AS
 SELECT team_name, match_id, sections_won
 FROM ( VALUES
 ( 'Team A', 6 , 4 ),
 ( 'Team B', 6 , 1 ),
 ( 'Team A', 19 , 4 ),
 ( 'Team A', 8 , 4 ),
 ( 'Team B', 19 , 1 ),
 ( 'Team B', 8 , 1 ),
 ( 'Team A', 7 , 4 ),
 ( 'Team B', 7 , 1 )
 ) AS t(team_name, match_id, sections_won);

It's for a game, so teams compete in matches. The match has a possible 5 points and in these examples 1 team is winning 4 of the 5 sections

I'd like to pivot this into a scoreboard where the report looks like this:

Name Match 6 Match 7 Match 8 Match 19
Team A 4 4 4 4
Team B 1 1 1 1

I've done this kind of thing before but I can't get this one to work for some reason.

The query looks a little like this. I tried to simplify for examples sake.

select *
from crosstab(
 $$
 select game.team_name::text, game.match_id, sum(game.rank)::int4
 from game
 where rank = 1 -- i only want the sections the team won, then sum above.
 group by 1,2
 $$
 ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;

The resulting data set looks like this though, which I don't get:

Name Match 6 Match 7 Match 8 Match 19
team a 4 null null null 
team b 1 null null null 
team a 4 4 null null
team b 1 1 null null
team a 4 null null null 
team b 1 null null null 

Any thoughts? I've spent several hours on this puppy so far. Driving me nuts! :)

• Evan Carroll: are sections_won and rank the same?
• jacklin: Thanks for the edit Evan! I should have done that probably.
• jacklin: oh wow! So adding the order by did work! @McNets I'm not sure I fully understand why though?
• McNets: @EvanCarroll answer is crystal clear.

Answer 1

You don't need the GROUP BY in a pivot... And, if you put that WHERE clause in there you're filtering out stuff you need.

SELECT *
FROM crosstab(
 $$
 SELECT game.team_name::text, game.match_id, game.sections_won
 FROM game
 ORDER by 1,2
 $$
 ) as ct("Team" text, "Match 6" int4 ,"Match 7" int4 ,"Match 8" int4 ,"Match 19" int4)
;
 Team | Match 6 | Match 7 | Match 8 | Match 19 
--------+---------+---------+---------+----------
 Team A | 4 | 4 | 4 | 4
 Team B | 1 | 1 | 1 | 1
(2 rows)

As to why you need the ORDER BY, from the docs

In practice the SQL query should always specify ORDER BY 1,2 to ensure that the input rows are properly ordered, that is, values with the same row_name are brought together and correctly ordered within the row. Notice that crosstab itself does not pay any attention to the second column of the query result; it's just there to be ordered by, to control the order in which the third-column values appear across the page.