How to translate column values to row values?

user_17614946 0
Viewed 56

Im trying to translate column values to rows in PostgreSQL. Every player can play maximum of 3 matches per series (bof2). For every game he has pokemon he picked and points he scored. Here is how it looks:

Player Pokemon Picked Picked Match Series Match id Points
Ash Charmander 1 213 101 43
Ash Bulbasor 2 213 102 52
Ash Pikachu 3 213 103 70
Brock Onyx 1 145 157 57
Brock Magmar 2 145 158 63
Gary Charmander 1 213 101 56
Gary Nidoking 2 213 102 36
Gary Magmar 3 213 103 34

Wanted Outcome:

Player First Match Pokemon First Match Points Second Match Pokemon Second Match Points Third Match Pokemon Third Match Points
Ash Charmander 43 Bulbasor 52 Pikachu 70
Brock Onyx 57 Magmar 63 null null
Gary Charmander 56 Nidoking 36 Magmar 34

I have tried using nth_element and ronumber() but cant figure it out with neither one.

postgresql sql
2 Answers

Based on picked column value retrieve first, second and third match pokemon and points. Use Player wise group by and MAX() function because showing single row.

-- PostgreSQL
SELECT Player
     , MAX(CASE WHEN Picked = 1 THEN Pokemon_Picked END) "First Match Pokemon"
     , MAX(CASE WHEN Picked = 1 THEN Points END) "First Match Points"
     , MAX(CASE WHEN Picked = 2 THEN Pokemon_Picked END) "Second Match Pokemon"
     , MAX(CASE WHEN Picked = 2 THEN Points END) "Second Match Points"
     , MAX(CASE WHEN Picked = 3 THEN Pokemon_Picked END) "Third Match Pokemon"
     , MAX(CASE WHEN Picked = 3 THEN Points END) "Third Match Points"
FROM table_name
GROUP BY Player
user_163646250

First off while what you want is doable in SQL, you are virtually always better off leaving the pivoting operation to your app's presentation manager. This is because that operation always requires a priori knowledge of the number of rows (at least per group). This makes maintenance for SQL implementation a problem (query must be rewritten), while app based pivoting may require no changes.
However there are several approaches. One is use the LEAD function to pick-off each succeeding value set, then compress the single row for each. (see demo)

with player_match_points ( player, pm1, pt1, pm2, pt2, pm3, pt3, rn) as   
     ( select player                       
            , pokemon                      
            , points                         
            , lead ( pokemon, 1) over (w)   
            , lead ( points,  1) over (w)  
            , lead ( pokemon, 2) over (w)   
            , lead ( points,  2) over (w)    
            , row_number (*)     over (w) 
         from matches 
        window w as (partition by player  
                         order by series, picked)
     ) 
select player   "Player"  
     , pm1      "First Match Pokemon"
     , pt1      "First Match Points"
     , pm2      "Second Match Pokemon"
     , pt2      "Second Match Points"
     , pm3      "Third Match Pokemon"
     , pt3      "Third Match Points"
  from player_match_points
 where rn=1;
user_76238560
Related