Re: Query Hint

On Feb 9, 2:55 pm, Rene Nyffenegger <rene.nyffeneg..._at_gmx.ch> wrote:
> On 2007-02-09, 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
>
> 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- Hide quoted text -
>
> - Show quoted text -

I think the original poster was looking for a more dynamic solution, where the number of columns displayed are dependant on the number of "LEVELS" generated by the query. I have used tom kyte's STRAGG function to produce similar reports.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:15637744429336 Received on Fri Feb 09 2007 - 15:27:44 CST