How to translate column values to row values?-CodePudding

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.

CodePudding user response：

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

CodePudding user response：

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;