"Andy O'Neill" <aon14nocannedmeat@lycos.co.uk> wrote in message news:vx6Md.161$89.17@fe3.news.blueyonder.co.uk...

<snip>

> You could possibly do this in sql by two unioned queries.
> I've done this when I want 12 months data cross tabbed/pivotted in a crystal report.
>

Why? That's so simple in standard ANSI SQL.

Given an overly simplified table definition for sales transactions:
CREATE TABLE Sales(
customerID integer not null
references Customers( customerID ),
productID integer not null
references Products( productID ),
saleDate date not null,
qty integer not null,
unitPrice numeric(11,2) not null,
totalSale as (qty * unitPrice),
primary key (customerID, productID, saleDate)
) ;

Then a query that produces a 12-month cross-tab of sales by product for a given year would be:

Select
productID,
productName,
sum((case when datepart( mm, saleDate ) = 1 then 1 else 0 end) * totalSale) as JanSales,
sum((case when datepart( mm, saleDate ) = 2 then 1 else 0 end) * totalSale) as FebSales,
sum((case when datepart( mm, saleDate ) = 3 then 1 else 0 end) * totalSale) as MarSales,
...
sum((case when datepart( mm, saleDate ) = 12 then 1 else 0 end) * totalSale) as DecSales
from
SALES
JOIN PRODUCTS
on Sales.productID = Products.productID
where
datepart( yy, saleDate ) = 2004
GROUP BY
productID, productName ;

Paul Horan
Sr. Architect
VCI Springfield, Mass
www.vcisolutions.com