Re: can I do this in sql

From: fumi <fumi_at_tpts5.seed.net.tw>
Date: 22 Oct 1999 16:23:48 GMT
Message-ID: <7uq32k$a36$7@news.seed.net.tw>

Bob <bookouri_at_my-deja.com> wrote in message news:7un9h9$4e2$1_at_nnrp1.deja.com...
> 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

 select id,

    max(decode(profile_type, 'HEIGHT', profile_code)) as height,
    max(decode(profile_type, 'WEIGHT', profile_code)) as weight,
    max(decode(profile_type, 'HAIR_COLOR', profile_code)) as hair,
    max(decode(profile_type, 'EYE_COLOR', profile_code)) as eyes,
    max(decode(profile_type, 'RACE', profile_code)) as race

  from off_prof
  where profile_type in ('HEIGHT', 'WEIGHT', 'HAIR_COLOR', 'EYE_COLOR', 'RACE')   group by id; Received on Fri Oct 22 1999 - 11:23:48 CDT