PostgreSQL Crosstab Query Help – How to Create Pivot Tables

pivotpostgresql

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! 🙂

Best Answer

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.

Related Question