CODEWITHSUNDEEP
sqlpostgresql
Bram
Bram

Reputation: 89

Creating a crosstab with multiple field values

This is an example of how my current table looks like:

game_id |player_id|player_name       |event_id|event_desc        |count  |
--------|---------|------------------|--------|------------------|-------|
1       |        1|player1           |       3|Shot              |      1|
1       |        1|player1           |       5|Rebound           |      3|
1       |        1|player1           |       7|Foul              |      1|
1       |        1|player1           |      14|Assist            |      1|
1       |        1|player1           |      17|Subbed in         |      4|
1       |        1|player1           |      18|Subbed out        |      3|
1       |        1|player1           |      19|Drew a Foul       |      2|
1       |        1|player1           |      20|Free Throws Scored|      3|
1       |        1|player1           |      21|Free Throws Missed|      1|
1       |        2|player2           |       3|Shot              |      7|
1       |        2|player2           |       4|Miss              |     10|
1       |        2|player2           |       5|Rebound           |      2|
1       |        2|player2           |       7|Foul              |      1|
1       |        2|player2           |      14|Assist            |      1|
1       |        2|player2           |      17|Subbed in         |      4|
1       |        2|player2           |      18|Subbed out        |      4|
1       |        2|player2           |      19|Drew a Foul       |      2|

I need to make a view based on this, making it so that I group the stats per game per player. The different stats are based on the count for that specific event_ID. I've got around 20 different ID's to map out.

game id | player_id | shot | Miss | Rebound |Foul | Assist | ...
1       |1          |1     |0     |3        |1    |1
1       |2          |7     |10    |2        |1    |1

I think I'd have to use crosstab functionality for this, but I'm not sure on how to exactly write out the code for this, I'm not really someone with knowledge in this field. If someone could help me with this, I'd greatly appreciate it.

Tried the following code:

select
    game_id,
    player_id,
    player_name,
    sum(case when event_desc = 'Jump Ball' then wjxbfs1 else 0) as Jump_Ball,
    sum(case when event_desc = 'Shot' then wjxbfs1 else 0) as Shot,
    sum(case when event_desc = 'Miss' then wjxbfs1 else 0) as Miss,
    sum(case when event_desc = 'Rebound' then wjxbfs1 else 0) as Rebound,
    sum(case when event_desc = 'Assist' then wjxbfs1 else 0) as Assist,
    sum(case when event_desc = 'Block' then wjxbfs1 else 0) as Block,
    sum(case when event_desc = 'Steal' then wjxbfs1 else 0) as Steal,
    sum(case when event_desc = 'Turnover' then wjxbfs1 else 0) as Turnover,
    sum(case when event_desc = 'Foul' then wjxbfs1 else 0) as Foul,
    sum(case when event_desc = 'Free Throws Taken' then wjxbfs1 else 0) as FT_Taken,
    sum(case when event_desc = 'Free Throws Scored' then wjxbfs1 else 0) as FT_Scored,
    sum(case when event_desc = 'Free Throws MIssed' then wjxbfs1 else 0) as FT_Missed,
    sum(case when event_desc = 'Timeout' then wjxbfs1 else 0) as Timeout,
    sum(case when event_desc = 'Violation' then wjxbfs1 else 0) as Violation,
    sum(case when event_desc = 'Subbed in' then wjxbfs1 else 0) as Subbed_In,
    sum(case when event_desc = 'Subbed out' then wjxbfs1 else 0) as Subbed_Out,
    sum(case when event_desc = 'Drew a Foul' then wjxbfs1 else 0) as Drew_Foul,
    sum(case when event_desc = 'Ejection' then wjxbfs1 else 0) as Ejected   
from
    stats
group by
    game_id,
    player_id,
    player_name

Returns the following error: SQL Error [42601]: ERROR: syntax error at or near ")" Position: 119

This was fixed by replacing "else 0" with "end".

Upvotes: 0

Views: 58

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269663

max(case when . . . end) is fine syntax. In Postgres, however, I've become fond of the filter keyword. This can also be expressed as:

select gameid, playerid, 
       max(count) filter (where event_desc = 'Shot') as shot,
       max(count) filter (where event_desc = 'Miss') as miss,
       . . .
from table_name
group by gameid, playerid;

Note that this is slightly faster, and the syntax is ISO/ANSI standard (although no available in any other databases, I think).

Upvotes: 1

Jose Bagatelli
Jose Bagatelli

Reputation: 1409

I'm not sure if postgresql supports pivot like SQLServer does. But in general I use case statements to pivot data. 

select
    game_id,
    player_id,
    player_name,
    sum(case when event_desc = 'shot' then count else 0 end) as shot,
    sum(case when event_desc = 'Miss' then count else 0 end) as Miss,
    -- and so forth for every single event_desc you want to pivot as a column
from
    current_table
group by
    game_id,
    player_id,
    player_name

That should do the trick for you.

Upvotes: 0

Zaynul Abadin Tuhin
Zaynul Abadin Tuhin

Reputation: 32003

you can try by using case when

select gameid,palyerid, 
max(case when event_desc='Shot' then count end) as shot,
max(case when event_desc='Miss' then count end) as Miss,
max(case when event_desc='Rebound' then count end) as Rebound,
max(case when event_desc='Foul' then count end) as Foul,
max(case when event_desc='Assist' then count end) as Assist,
------
from table_name group by  gameid,palyerid

Upvotes: 1

Related Questions