Re: can I do this in sql

I actually managed to stumble across this exact solution with the max and a group by....it turns out to be extremely slow by the time I get it all together so Im looking at what changes I might be able to make to make it faster... Ill try the other suggested select statement and see if there is a diff in performance..

thanks for the reply

> The following will transpose this for you:
>
> select id,
>
> max(decode(off_prof.profile_type,'HEIGHT',off_prof.profile_code,null))
height,
>
> max(decode(off_prof.profile_type,'WEIGHT',off_prof.profile_code,null))
weight,
>
> max(decode

(off_prof.profile_type,'HAIR_COLOR',off_prof.profile_code,null))Hair,
>
> max(decode

(off_prof.profile_type,'EYE_COLOR',off_prof.profile_code,null))Eyes,
> max(decode

(off_prof.profile_type,'RACE',off_prof.profile_code,null))Race
> from off_prof
> group by id
> /
>
> be careful if you use the join below as if you are missing any of the
attributes
> -- NO row for that user would be returned.
>
> >> this give me a row for each profile type for each individual, what
I
> >> need to see is one row per individual with all the identifiers.
> >>
> >> any suggestions would be appreciated
> >>
> >> Sent via Deja.com http://www.deja.com/
> >> Before you buy.
> >>
> >
> >Try this:
> >
> >select a.id, a.height, b.weight, c.hair_color, d.eye_color, e.race
> >from off_prof a, off_prof b, off_prof c, off_prof d, off_prof e
> >where a.id = b.id and b.id = c.id and c.id = d.id and d.id = e.id and
> > a.profile_type = 'HEIGHT' and b.profile_type = 'WEIGHT' and
> > c.profile_type = 'HAIR_COLOR' and d.profile_type = 'EYE_COLOR'
and
> > e.profile_type = 'RACE'
> >
> >Hope this helps.
> >
> >Yar
> >
> >
> >Sent via Deja.com http://www.deja.com/
> >Before you buy.
>
> --
> See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to
Oracle8i'...
> Current article is "Part I of V, Autonomous Transactions" updated
June 21'st
>
> Thomas Kyte tkyte_at_us.oracle.com
> Oracle Service Industries Reston, VA USA
>
> Opinions are mine and do not necessarily reflect those of Oracle
Corporation
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Oct 22 1999 - 05:51:36 CDT