Thanks Jonathan
Jay
"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
news:cdj8qc$98q$1_at_hercules.btinternet.com...
>
> There are a couple of options - that still look
> quite ugly, but don't do lots of self-joins.
>
> The traditional option was to use the decode()
> function to produce a "diagonal" output that you
> then aggregated.
>
> Assume a table with:
> object_id, col_id, col_value
> and you want to get 'pseudo-columns'
> 1,5,9,11 for object "X"
>
> Step 1 of method:
> ------------------
> select
> object_id, col_id, col_value
> from
> tabX
> where
> object_id = 'X'
> and col_id in (1,5,9,11)
> ;
>
> Step 2: rewrite with decodes()
> --------------------------------
> select
> object_id,
> decode(col_id,1,col_value,null) val1,
> decode(col_id,5,col_value,null) val2,
> decode(col_id,9,col_value,null) val3,
> decode(col_id,11,col_value,null) val4
> from
> where
> object_id = 'X'
> and col_id in (1,5,9,11)
> ;
>
> Step 3: collapse N lines to 1
> -----------------------------
> select
> object_id,
> max(val1), max(val2), max(val3), max(val4)
> from
> (
> query in step 2
> )
> group by
> object_id
> ;
>
>
> See also asktom.oracle.com for further details
> and alternative methods.
>
>
> --
> Regards
>
> Jonathan Lewis
>
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
> The Co-operative Oracle Users' FAQ
>
> http://www.jlcomp.demon.co.uk/seminar.html
> Optimising Oracle Seminar - schedule updated July 14th
>
>
>
> "Jay" <me_at_heyjay.com> wrote in message
> news:YKCdnT6QeLxavmDdRVn-rQ_at_speakeasy.net...
> > > When you find a piece of paper wide enough to print 1000 columns
please
> > > let me know. ;-)
> >
> > I agree with you, I never want to print lots of columns.
> >
> > But the queries do get ugly when you want to do aggragations, unless I'm
> > doing
> > it wrong. I always end up with things like:
> >
> > select sum(first_col)
> > from (
> > select a.col_value 'first_col', b.col_value '2nd_col'
> > from normalized_table a, normalized_table b
> > where a.id = b.id
> > and first_col > X
> > )
> > group by 2nd_col
> >
> > Is there a better way?
> >
> > Jay
> >
> >
>
>
Received on Tue Jul 20 2004 - 09:35:17 CDT
