simple question - multiple rows in one line with simple query

Hi

I am mostly self schooled in the art of making SQL queries so I sometimes have trouble in doing basic things.

What I need is the following

From a table conatining inventory values pr month and year i need to make a report with all the months on one line:

Table:
Item, year, month , inventory

Report:
Item, January inventory, Febr Inv, March inv, ... , december inventory

Now one way would be to join the table 12 times - but not all items have inventory values in all months so I can't see that working.

I'm using Oracle, and I solved the problem by adding a stored procedure (inventory) whic fetched the value for each month like this:

select

    i.item,

    inventory(item,1,2005),
    inventory(item,2,2005),
    inventory(item,3,2005),
    inventory(item,4,2005),
    inventory(item,5,2005),
    inventory(item,6,2005),
    inventory(item,7,2005),
    inventory(item,8,2005),
    inventory(item,9,2005),
    inventory(item,10,2005),
    inventory(item,11,2005),
    inventory(item,12,2005)

    (select distinct item from inventorytable where year=2005) i;

But isn't there a way to do the same with a more simple query?

Cheers

Soren Received on Tue Feb 15 2005 - 08:48:19 CST