Re: simple question - multiple rows in one line with simple query

"Søren Kongstad" <kongstad_at_kongstad.net> wrote:

>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)
>from
> (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
>

The only other way I know of would be to build a view that uses inline queries like: Create or replace view V_Inventory_report as select item,(select inventory from inventorytable where month = 1 and year = 2005) as JanInv, (select inventory from inventorytable where month = 2 and year = 2005) as FebInv, (select inventory from inventorytable where month = 3 and year = 2005) as MarInv, etc..  

Your method seems better... Received on Tue Feb 15 2005 - 09:25:20 CST