Postgres 14

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

select version();


version


PostgreSQL 14.1 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 8.5.0 20210514 (Red Hat 8.5.0-4), 64-bit


create table matches 
       ( player 	text
       , pokemon 	text 
       , picked 	integer
       , series 	integer 
       , match_id 	integer 
       , points     integer 
       ) ; 
      
 insert into matches(player, pokemon, picked, series, match_id, points) 
     ( values ('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)
     ) ;
8 rows affected

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; 


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

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