An example of how to change row data into columnar data

Alan Davies wrote:

Using FB 2.1 - no problems with FB itself, working great. Hi, I'm not sure if that expalins what I want to do. I have an SP which returns all the data I require but in a record-by-record format, as follows

begin
   for select  w.date_in,
               gl.gl_name,gl.gl_code,
               coalesce(sum(w.tonnes),0)
   from        weigh w
   join        gl_codes gl
   on          gl.gl_code=w.fuel_code
   where       w.date_in between :fromdate and :uptodate
   group by    w.date_in,
               gl.gl_code,gl.gl_name
   into        :date_in,
               :gl_name,
               :gl_code,
               :tonnes
   do suspend;
end

and this is the result (ignore any formatting that's a cut & paste matter) Notice that daily data can be any combination of GL_NAME

DATE_IN     GL_CODE GL_NAME                TONNES
05/15/2009  501110  Contract Litter        1234.02
05/15/2009  501120  Contract Biomass        878.7
05/15/2009  501210  Spot Litter             48.34
05/15/2009  501220  Spot Biomass           514.69
05/16/2009  501110  Contract Litter           865
05/16/2009  501120  Contract Biomass        36.36
05/18/2009  501110  Contract Litter         391.5
05/18/2009  501120  Contract Biomass        667.64
05/18/2009  501220  Spot Biomass            505.36
05/19/2009  501110  Contract Litter         233.73
05/19/2009  501120  Contract Biomass        442.79
05/19/2009  501220  Spot Biomass            410.72

What I want is this:

DATE_IN      Contract Litter Contract Biomass Spot Litter Spot Biomass
05/15/2009   1234.02         878.7            48.34       514.69
05/16/2009   865             36.36            0           0
05/18/2009   391.5          667.64            0           505.36

I thought about creating a table with this layout and populating it by selecting from the existing SP in a second SP, such as

begin
insert into newtable
 select distinct(date_in) from SP;
 update newtable
 set Contract Litter=(select tonnes from SP where
SP.date_in=newtable.date_in  and SP.GL_NAME=newtable.GL_NAME) (you getthe idea)

But I think there is a way to do this with a procedure using while..next. Am I right in thinking this or should I just carry on with creating the new table?

Dimitry Sibiryakov answers:

Usually such things are called "cross-table" and are done by report tool. Only if number of columns in result is fixed you can try to use CASE for tossing sums into them.

Svein Erling Tysvaer answers:

I agree with Dimitry in that using a report tool is the standard way of solving a problem like yours. However, it is possible to write a statement that at least gets you the sums spread across columns. First, there are two things that I don't know how to get into one single statement:

1. Returning a random number of columns You basically have to decide how many columns you want, although they can of course be if there is less columns than you anticipate.
2. Name the columns depending on row values This is possible to do by using EXECUTE STATEMENT, but I'll leave this exercise for you to discover yourself.

Here's the main statement, it involves one WITH RECURSIVE to get the columns, one WITH to get the sums and another WITH to get the dates (I assume all codes may be 0 for any given day, so I cannot assume that the first column will always contain a value, although your particular example has a value for 'contract litter' for all dates) - I've written so that four columns should be catered for:

with recursive W as
(select w1.fuel_code, cast(1 as Integer) as ColumnNo
  from weight w1
  where w1.date_in between :fromdate and :uptodate
    and not exists(select * from weight w2
                   where w2.w1.fuel_code < w1.w1.fuel_code
                     and w2.date_in between :fromdate and :uptodate)
  union all
  select w4.fuel_code, w3.ColumnNo+1
  from w w3
  join weight w4 on w3.fuel_code < w4.fuel_code
  where w4.date_in between :fromdate and :uptodate
    and not exists(select * from weight w5
    where w5.date_in between :fromdate and :uptodate
      and w5.fuel_code between w3.fuel_code+1 and w4.fuel_code-1)),

  MySums as(select w1.fuel_code, w1.date_in,
            coalesce(sum(w1.tonnes),0) as Tonnes
            from weight w1
            where w1.date_in between :fromdate and :uptodate
            group by 1, 2),

  MyDates as (select distinct w1.date_in
              from weight w1
              where w1.date_in between :fromdate and :uptodate)

select md1.date_in, coalesce(sum(ms1.tonnes),0),
                     coalesce(sum(ms2.tonnes),0),
                     coalesce(sum(ms3.tonnes),0),
                     coalesce(sum(ms4.tonnes),0)
from MyDates md1
left join w w1 on w1.ColumnNo = 1
left join MySums ms1
        on w1.fuel_code = ms1.fuel_code
       and md1.date_in = ms1.date_in
left join w w2 on w2.ColumnNo = 2
left join MySums ms2
        on w2.fuel_code = ms2.fuel_code
       and md2.date_in = ms2.date_in
left join w w3 on w3.ColumnNo = 3
left join MySums ms3
        on w3.fuel_code = ms3.fuel_code
       and md3.date_in = ms3.date_in
left join w w4 on w4.ColumnNo = 4
left join MySums ms4
        on w4.fuel_code = ms4.fuel_code
       and md4.date_in = ms4.date_in

Unfortunately, I haven't tested this statement and it may contain errors. I've no clue about performance, and I think most people would agree with me that a reporting tool creating pivot tables could be simpler to understand.