Re: Query Hint

On 2007-02-09, ciccio <ciccio_at_ciccio.it> wrote:
> Hi,
>
> I have a ORDERS table as follows:
>
> ID NUMBER(10)
> ORDER_DATE DATE
> DEPARTMENT NUMBER(10)
> LEVEL NUMBER(2)
>
> If I perform a query like this one:
>
> select department, level, count(*) as QUANTITY from orders
> where level in (select id from LEVELS)
> and (ORDER_DATE between to_date('20012007000000','DDMMYYYYHH24MISS') and
> to_date('21012007000000','DDMMYYYYHH24MISS'))
> group by DEPARTMENT, LEVEL
> /
>
> I have this (correct) answer:
>
> DEPARTMENT LEVEL QUANTITY
> ----------- ---------------- ----------
> 11 1 44
> 11 2 81
> 11 3 4
> 11 4 6
> 11 5 3
> 12 1 36
> 12 2 84
> 12 4 3
> 12 5 12
>
>
> I'd like to have it in another way, this one:
>
> DEPARTMENT LEVEL_1 LEVEL_2 LEVEL_3 LEVEL_4 LEVEL_5
> ---------- ------- ------- ------- ------- -------
> 11 44 81 4 6 3
> 12 36 84 0 10 12

I believe you're after a pivot query. May I refer to my website for such a thing:
  http://www.adp-gmbh.ch/ora/sql/examples/pivot.html

hth,
Rene

-- 
  Rene Nyffenegger
  http://www.adp-gmbh.ch