Re: can I do this in sql

>In article <7un9h9$4e2$1_at_nnrp1.deja.com>,
> Bob <bookouri_at_my-deja.com> wrote:
>> For some reason our database has race, hgt, wgt, hair color, eye color
>> in a "profile" table. I need a view that gives me all that
>information
>> on each individual on one row.
>>
>> I can select all the data i need with:
>> select id,
>> decode(off_prof.profile_type,'HEIGHT',off_prof.profile_code)height,
>> decode(off_prof.profile_type,'WEIGHT',off_prof.profile_code)weight,
>>
>decode(off_prof.profile_type,'HAIR_COLOR',off_prof.profile_code)Hair,
>>
>decode(off_prof.profile_type,'EYE_COLOR',off_prof.profile_code)Eyes,
>> decode(off_prof.profile_type,'RACE',off_prof.profile_code)Race
>>
>> from
>>
>> off_prof
>>
>> where
>>
>> (profile_type = 'HEIGHT'or profile_type = 'WEIGHT' or
>> profile_type = 'HAIR_COLOR'or profile_type = 'EYE_COLOR' or
>> profile_type = 'RACE')
>>

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 Received on Thu Oct 21 1999 - 13:17:24 CDT