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