Re: Was: maximum number of columns per table

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Tue, 20 Jul 2004 14:05:00 +0000 (UTC)
Message-ID: <cdj8qc$98q$1@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:05:00 CDT