Lee Jenkins wrote:

>
>
>
> Hello all, I know that SQL DB's don't have inherent Crosstab capabilities
> and I need to create a report for a customer that is setup like so:
>
> Product Department/Category/Name Jan Feb March Total
> Beverages
> Coke 33.33 44.44 55.55 133.32
> Sprite 33.33 44.44 55.55 133.32
>
> Summary Band =======================> xx.xx xx.xx xx.xx xxx.xx
>
> I have tried two ways so far. One, I have been populating a mem table
> manually with the values which is very close since there are many separate
> queries being run. Secondly, I have tried the query below which is
> faster,
> just not as fast as I'd like it. Can there be any improvement on the
> query?
> I have included the PLAN as well.

One method I have read about ( in SQL for SMARTIES by Joe Celko) is to
create an identity matrix for the months and then join that to the Sales
table ( ItemsPermanent2).

The matrix should look something like this:
M Jan Feb Mar Apr ......
1 1 0 0 0
2 0 1 0 0
3 0 0 1 0
4 0 0 0 1
...

The query would then be something like:
SELECT
IP.PRODUCTDESCRIPTION,
IP.ProductID,
IP.CATEGORY,
FD.FoodDepartmentName,
Sum(IP.ItemPrice*c.Jan),
Sum(IP.ItemPrice*c.Feb),
Sum(IP.ItemPrice*c.Mar).....
From
ItemsPermanent2 IP, crosstab as c
Join FoodDepartments FD ON (FD.fooddepartmentid = IP.FoodDepartmentID)
where EXTRACT(MONTH FROM IP.ItemDate) = c.M
GROUP BY
FD.FoodDepartmentName,
IP.PRODUCTDESCRIPTION,
IP.ProductID,
IP.CATEGORY
ORDER BY
FD.FoodDepartmentName,
IP.CATEGORY,
IP.PRODUCTDESCRIPTION

This is as I said an approach I have read about rather than used as I
have been lucky enough to have a report writting tool server side when
I've needed to do this sort of thing.
If you can get it working this should be faster as it is doing a whole
lot less in terms of all those Sub Selects

Regards
Andy