Difficult 1

[Leighton21]

Hi All,

I have a table on which I would like to report on. The table is however difficult to get this particular information out of. As an example I will give you a few rows from this table

RecNum | ProductCode | PeriodDate | SLSQuantity
1 COMP 30/04/04 10
2 COMP 31/05/04 12
3 Del 30/04/04 1

As you can see It basically shows monthly sales statistics for products. What I am trying to do is create a report which has the products running down the side and the month running across the top i.e.
JAN FEB MARCH etc...
COMP 1 2 3
DEL ...

Looking at the data however you can see that there are multiple entries of the same product(one for each month) what I would like to do is Combine the entries for the same product and have the sales quantities for each month, this looks near on impossible since I would have to combine the same column for multiple entries (i.e. find all the entries for period date for productcode COMP and insert them into a single row. The question arises how would I know which productcode the PeriodDate would belong to.

Can this be achieved through an SQL Query

Can anyone please help

 

[PHV]

Depending of your RDBMS you may search the documentation for CrossTable query or PivotTable.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Leighton21]

Thanks for the quick response PHV