Summarize unique column in row

I’m new to Firebird and need your help.

I have a stored procedure with following reduced output:

Player	Team	Number
Reus	Ahlen	18
Lewandowski	Posen	19
Reus	MG	11
Reus	BVB	11
Lewandowski	BVB	9
Haaland	BVB	9

I want to summarize the Players and transform Team&Number to a new column.

The output should be:

Player	Station 1	Station 2	Station 3
Reus	Ahlen 18	MG 11	BVB 11
Lewandowski	Posen 19	BVB 9
Haaland	BVB 9

I am using Firebird 2.5.8

Answer

For the remainder of this answer, I’m using the following setup:

create table player (
  player varchar(100) not null,
  team varchar(100) not null,
  number smallint not null
);

commit;

insert into player (player, team, number) values ('Reus', 'Ahlen', 18);
insert into player (player, team, number) values ('Lewandowski', 'Posen', 19);
insert into player (player, team, number) values ('Reus', 'MG', 11);
insert into player (player, team, number) values ('Reus', 'BVB', 11);
insert into player (player, team, number) values ('Lewandowski', 'BVB', 9);
insert into player (player, team, number) values ('Haaland', 'BVB', 9);

commit;

The examples assume that the player name is sufficient to uniquely identify the player.

Because of the nature of Firebird’s DSQL dialect and implementation of query execution, the number of ‘station’ columns is fixed. In this example, I’m using three, like your question, but this can be extended to more columns if necessary. In Firebird 2.5, generating the desired pivot table is messy, and will likely not perform well.

A basic, pure SQL solution, would be something like:

with unique_players as (
  select distinct player from player
)
select 
  player,
  (select team || ' ' || number 
   from player 
   where player = unique_players.player 
   order by number desc
   rows 1) as station_1,
  (select team || ' ' || number 
   from player 
   where player = unique_players.player 
   order by number desc
   rows 2 to 2) as station_2,
  (select team || ' ' || number 
   from player 
   where player = unique_players.player 
   order by number desc
   rows 3 to 3) as station_3
from unique_players;

In this example, we determine the unique players, and then select the first, second and third station using a subquery using rows (you can also use first/skip, or, in Firebird 3.0 and higher, offset/fetch)

An alternative, which likely performs better, is using a stored procedure or execute block, but the code does get more complex.

execute block 
  returns (
    player type of column player.player, 
    station_1 varchar(120), 
    station_2 varchar(120), 
    station_3 varchar(120)
  )
as
  declare station smallint = 0;
  declare current_player type of column player.player;
  declare current_team type of column player.team;
  declare current_number type of column player.number;
begin
  for select player, team, number 
      from player 
      order by player, number desc
      into current_player, current_team, current_number
  do
  begin
      if (current_player is distinct from player) then
      begin
         -- output row when player changes
         if (player is not null) then suspend;
         station = 1;
         player = current_player;
         station_1 = current_team || ' ' || current_number;
      end
      else
      begin
        station = station + 1;
        if (station = 2) then
        begin
          station_2 = current_team || ' ' || current_number;
        end
        else if (station = 3) then
        begin
          station_3 = current_team || ' ' || current_number;
        end
        -- rows for station > 3 are ignored
      end
  end
  -- output final player
  if (player is not null) then suspend;
end

This iterates over the rows, populating the output columns of the execute block, outputting them when the next player is found.

On the other hand, in Firebird 4.0, you could do something like:

Using NTH_VALUE window function:

select player, station_1, station_2, station_3
from (
  select 
    player, 
    row_number() over player_order as rownum,
    nth_value(team || number, 1) over player_order as station_1,
    nth_value(team || number, 2) over player_order as station_2,
    nth_value(team || number, 3) over player_order as station_3
  from player
  window player_order as (
    partition by player 
    order by number desc 
    rows between unbounded preceding and unbounded following)
)
where rownum = 1

Or using filtered aggregate functions:

with player_stations as (
  select 
    player, 
    team || ' ' || number as station, 
    row_number() over (partition by player order by number desc) as rownum
  from player
)
select 
  player, 
  max(station) filter (where rownum = 1) as station_1,
  max(station) filter (where rownum = 2) as station_2,
  max(station) filter (where rownum = 3) as station_3
from player_stations
group by player

Using a lateral join:

with player_stations as (
  select player, number, team || ' ' || number as station from player
)
select 
  player, 
  st1.station as station_1, 
  st2.station as station_2, 
  st3.station as station_3
from (select distinct player from player) p
left join lateral (
  select station
  from player_stations
  where player = p.player
  order by number desc
  fetch first row only
) st1 on true
left join lateral (
  select station
  from player_stations
  where player = p.player
  order by number desc
  offset 1 row
  fetch next row only
) st2 on true
left join lateral (
  select station
  from player_stations
  where player = p.player
  order by number desc
  offset 2 rows
  fetch next row only
) st3 on true

This is is similar to the first example, but pushing down the selection of the stations into the lateral join (a feature introduced in Firebird 4.0).