Svein that's a fantastic response. Far more detailed than I'd ever
imagined. I will use that as a basis. It's not just this example, either,
there are a number of similar requirements. So I'm very pleased.
Regards Alan

> Hi Alan!
>
> 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 <null> 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.
>
> HTH,
> Set
>
> 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 get
>> the 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?
>>
>> Thanks Alan
>
>