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')
>
> 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. Received on Thu Oct 21 1999 - 10:53:32 CDT