Re: Query Hint

On Feb 9, 3:15 am, ciccio <cic..._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
>
> How can I write such a query?
>
> Thanks in advance

If the number of levels is fixed a rather simple approach would be:

select department,
sum(decode(level,1,quantity,0)) as level_1, sum(decode(level,2,quantity,0)) as level_2, ...
from orders
...
group by department Received on Fri Feb 09 2007 - 15:59:01 CST