Add new row for first of same group

Question

I'm new to Firebird and need your help. I know that Firebird's task isn't to do a nice print.

I have a stored procedure with following reduced output:

Drink	Name
Cola	Marcus
Cola	Siegfried
Fanta	Jochen
Beer	Anna
Beer	Manuel

I want to add a new row and column before the first value of each group of Drink with the name of the Drink in it.

So the output should be:

Drink	Drinks for print	Name
Cola	Cola
Cola		Marcus
Cola		Siegfried
Fanta	Fanta
Fanta		Jochen
Beer	Beer
Beer		Anna
Beer		Manuel

I am using Firebird 2.5.8

Answer 1

Assuming you don't want to modify the existing stored procedure, but instead want to 'decorate' the output of the stored procedure, you would need to do something like this:

execute block
  returns (
    drink varchar(50), 
    "Drink for print" varchar(50), 
    name varchar(50))
as
  declare drink_intermediate varchar(50);
  declare name_intermediate varchar(50);
begin
  for select drink, name 
    from drinks 
    order by drink 
    into drink_intermediate, name_intermediate
  do
  begin
    if (drink is distinct from drink_intermediate) then
    begin
        drink = drink_intermediate;
        "Drink for print" = drink_intermediate;
        name = null;
        suspend;
        "Drink for print" = null;
    end
    name = name_intermediate;
    suspend;
  end
end

That is, each time the drink changes, you need to add an extra row.

You can also do something like this in DSQL, but this probably more expensive when you have a lot of rows due to the distinct:

select distinct 
  drink, 
  case a.type when 1 then drink end as "Drink for print", 
  case a.type when 2 then name end as name
from drinks
cross join (
  select 1 as type from RDB$DATABASE
  union all
  select 2 as type from RDB$DATABASE
) a
order by drink, type

That is, for each row of the stored procedure, we add an extra row with the "Drinks for print" column populated and the name not populated, and then we take a distinct (to remove the repeated rows), and sort for a consistent output.