How do I fill in null values with all rows based on group in Oracle SQL 19?

Question

I have the following table in Oracle SQL 19:

+--------------+--------------+-------------------+------------------+-------------------+
|     Team     | Team Ranking | Team Ranking Date | Player Join Date |      Player       |
+--------------+--------------+-------------------+------------------+-------------------+
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Lionel Messi      |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Luis Suarez       |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Antoine Griezmann |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Ousmane Dembele   |
| FC Barcelona |            3 | 16-Jul-20         | (null)           | (null)            |
| FC Barcelona |            5 | 17-Jul-20         | (null)           | (null)            |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Eden Hazard       |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Sergio Ramos      |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Karim Benzema     |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Gareth Bale       |
| Real Madrid  |            2 | 15-Jul-20         | 16-Jul-20        | James Rodriguez   |
| Real Madrid  |            5 | 25-Jul-20         | (null)           | (null)            |
| Real Madrid  |            2 | 28-Jul-20         | (null)           | (null)            |
+--------------+--------------+-------------------+------------------+-------------------+

I want to fill in the null values in the above table with all the players' information from the team. In other words, I want the result to look like the following:

+--------------+--------------+-------------------+------------------+-------------------+
|     Team     | Team Ranking | Team Ranking Date | Player Join Date |      Player       |
+--------------+--------------+-------------------+------------------+-------------------+
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Lionel Messi      |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Luis Suarez       |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Antoine Griezmann |
| FC Barcelona |            1 | 15-Jul-20         | 15-Jul-20        | Ousmane Dembele   |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Lionel Messi*     |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Luis Suarez*      |
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Antoine Griezmann*|
| FC Barcelona |            3 | 16-Jul-20         | 15-Jul-20*       | Ousmane Dembele*  |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Lionel Messi*     |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Luis Suarez*      |
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Antoine Griezmann*|
| FC Barcelona |            5 | 17-Jul-20         | 15-Jul-20*       | Ousmane Dembele*  |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Eden Hazard       |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Sergio Ramos      |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Karim Benzema     |
| Real Madrid  |            2 | 15-Jul-20         | 15-Jul-20        | Gareth Bale       |
| Real Madrid  |            2 | 15-Jul-20         | 16-Jul-20        | James Rodriguez   |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Eden Hazard*      |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Sergio Ramos*     |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Karim Benzema*    |
| Real Madrid  |            5 | 25-Jul-20         | 15-Jul-20*       | Gareth Bale*      |
| Real Madrid  |            5 | 25-Jul-20         | 16-Jul-20*       | James Rodriguez*  |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Eden Hazard*      |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Sergio Ramos*     |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Karim Benzema*    |
| Real Madrid  |            2 | 28-Jul-20         | 15-Jul-20*       | Gareth Bale*      |
| Real Madrid  |            2 | 28-Jul-20         | 16-Jul-20*       | James Rodriguez*  |
+--------------+--------------+-------------------+------------------+-------------------+

I starred the filled-in values in the above table.

I only showed two ranking changes for each team in this example. But the rankings for a team can change indefinite number of times.

Can someone show me how to do this in Oracle SQL 19?

Any help would be greatly appreciated!

Below is the sql query to generate the table in Oracle for your convenience:

with tbl as (
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Lionel Messi' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Luis Suarez' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Antoine Griezmann' player from dual union
    select 'FC Barcelona' team, 1 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Ousmane Dembele' player from dual union
    select 'FC Barcelona' team, 3 team_ranking, to_date('7/16/2020 04:18', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'FC Barcelona' team, 5 team_ranking, to_date('7/17/2020 06:54', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Eden Hazard' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Sergio Ramos' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Karim Benzema' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/15/2020 11:31', 'MM/DD/YYYY HH24:MI') player_join_date,  'Gareth Bale' player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/15/2020 09:29', 'MM/DD/YYYY HH24:MI') team_ranking_date, to_date('7/16/2020 14:44', 'MM/DD/YYYY HH24:MI') player_join_date,  'James Rodriguez' player from dual union
    select 'Real Madrid' team, 5 team_ranking, to_date('7/25/2020 10:02', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual union
    select 'Real Madrid' team, 2 team_ranking, to_date('7/28/2020 06:13', 'MM/DD/YYYY HH24:MI') team_ranking_date, null player_join_date,  null player from dual
)
select *
from tbl
order by team, team_ranking_date, player_join_date

Answer 1

If I understand correctly, you have two "entities" that you want to combine: the list of distinct teams and rankings on the one hand and the list of players on the other.

The idea is to select the distinct values for each one of these and then cross join them:

select *
from (select distinct team, team_ranking, team_ranking_date from tbl) t  join
     (select distinct team, player_join_date, player
      from tbl
      where player is not null
     ) p
     using (team)
order by team, team_ranking_date, player_join_date;

Here is a db<>fiddle.

Answer 2

If you only have one table (as you wrote in your question), you should create tables for TEAMS, PLAYERS, TEAMPLAYERS, and TEAMRANKINGS. Once you have these tables, you can write a query that gives you the required result. (You only need to create the tables once! Run the query as often as you want ...)

Tables

create table teams
as
select unique team from tbl ; -- TBL: your original table

create table players 
as
select unique player from tbl where player is not null ;

-- map players <-> teams
create table teamplayers
as
select unique team, player, player_join_date 
from tbl
where player is not null ;

-- each team will have multiple rankings
create table teamrankings
as
select unique team, team_ranking, team_ranking_date
from tbl ;

-- check: 
select * from teams ;        -- 2 rows
select * from players ;      -- 9 rows
select * from teamplayers ;  -- 9 rows
select * from teamrankings ; -- 6 rows

Query

select 
  T.team
, TR.team_ranking
, TR.team_ranking_date
, TP.player_join_date
, P.player
from teams T
  join teamplayers TP  on T.team = TP.team
  join players P       on TP.player = P.player
  join teamrankings TR on T.team = TR.team
order by T.team, TR.team_ranking_date, P.player
; 

Result

           TEAM    TEAM_RANKING    TEAM_RANKING_DATE    PLAYER_JOIN_DATE               PLAYER 
_______________ _______________ ____________________ ___________________ ____________________ 
FC Barcelona                  1 15-JUL-20            15-JUL-20           Antoine Griezmann    
FC Barcelona                  1 15-JUL-20            15-JUL-20           Lionel Messi         
FC Barcelona                  1 15-JUL-20            15-JUL-20           Luis Suarez          
FC Barcelona                  1 15-JUL-20            15-JUL-20           Ousmane Dembele      
FC Barcelona                  3 16-JUL-20            15-JUL-20           Antoine Griezmann    
FC Barcelona                  3 16-JUL-20            15-JUL-20           Lionel Messi         
FC Barcelona                  3 16-JUL-20            15-JUL-20           Luis Suarez          
FC Barcelona                  3 16-JUL-20            15-JUL-20           Ousmane Dembele      
FC Barcelona                  5 17-JUL-20            15-JUL-20           Antoine Griezmann    
FC Barcelona                  5 17-JUL-20            15-JUL-20           Lionel Messi         
FC Barcelona                  5 17-JUL-20            15-JUL-20           Luis Suarez          
FC Barcelona                  5 17-JUL-20            15-JUL-20           Ousmane Dembele      
Real Madrid                   2 15-JUL-20            15-JUL-20           Eden Hazard          
Real Madrid                   2 15-JUL-20            15-JUL-20           Gareth Bale          
Real Madrid                   2 15-JUL-20            16-JUL-20           James Rodriguez      
Real Madrid                   2 15-JUL-20            15-JUL-20           Karim Benzema        
Real Madrid                   2 15-JUL-20            15-JUL-20           Sergio Ramos         
Real Madrid                   5 25-JUL-20            15-JUL-20           Eden Hazard          
Real Madrid                   5 25-JUL-20            15-JUL-20           Gareth Bale          
Real Madrid                   5 25-JUL-20            16-JUL-20           James Rodriguez      
Real Madrid                   5 25-JUL-20            15-JUL-20           Karim Benzema        
Real Madrid                   5 25-JUL-20            15-JUL-20           Sergio Ramos         
Real Madrid                   2 28-JUL-20            15-JUL-20           Eden Hazard          
Real Madrid                   2 28-JUL-20            15-JUL-20           Gareth Bale          
Real Madrid                   2 28-JUL-20            16-JUL-20           James Rodriguez      
Real Madrid                   2 28-JUL-20            15-JUL-20           Karim Benzema        
Real Madrid                   2 28-JUL-20            15-JUL-20           Sergio Ramos  

You should also add some constraints eg

-- team names must be unique
alter table teams 
add constraint teams_unique unique( team ) ;

-- player names must be unique
alter table players 
add constraint players_unique unique( player ) ;

-- allow the user to add rankings for existing teams only
alter table teamrankings
add constraint team_fkey foreign key( team ) 
references teams( team ) ;

-- (existing) players can be added to existing teams only
alter table teamplayers
add (
  constraint teamplayers_team_fkey foreign key( team )
    references teams( team )
, constraint teamplayers_player_fkey foreign key( player )
    references players( player ) 
) ;

If you want to add more rankings, you just need to INSERT into the TEAMRANKINGS table, and run the same query as before (see DBfiddle). Notice that you do NOT need to insert the PLAYER_JOIN_DATE and PLAYER now - which was the problem with your original table (namely that you had to insert NULLs when you added a new ranking).

insert into teamrankings( team, team_ranking, team_ranking_date )
values( 'Real Madrid', 7, sysdate ) ;

Alternative

If you cannot create new tables for whatever reason, you could also use the same query as above, but instead of the table names, drop in the queries that we have used for creating the 4 tables:

-- TBL: your original table
select 
  T.team
, TR.team_ranking
, TR.team_ranking_date
, TP.player_join_date
, P.player
from ( select unique team from tbl ) T
  join ( 
    select unique team, player, player_join_date 
    from tbl
    where player is not null 
  ) TP on T.team = TP.team
  join (
    select unique player from tbl where player is not null 
  ) P  on TP.player = P.player
  join ( 
    select unique team, team_ranking, team_ranking_date
    from tbl
  ) TR on T.team = TR.team
order by T.team, TR.team_ranking_date, P.player
;

DBfiddle here.

Answer 3

You need to consider seperately the sets with null players and non-null players. Take the sets with non-null players directly. And apply outer and cross joins for the rest, and combine all the results through UNION ALL like below :

with  tbl2 as
  (    
    select * from tbl where player is not null
    union all
    select tt1.team, tt2.team_ranking, tt2.team_ranking_date, tt1.player_join_date, tt1.player
      from
      (
       (select t2.*
          from ( select * from tbl where player is null ) t1
         right join ( select * from tbl where player is not null ) t2 on t2.player = t1.player ) tt1
       cross join      
       (select t1.*
          from ( select * from tbl where player is null ) t1
         left join ( select * from tbl where player is not null ) t2 on t2.player = t1.player ) tt2
      )          
  )
 select * 
   from tbl2 
  order by team, team_ranking_date, player_join_date