Re: Rows to Columns

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 1997/05/03
Message-ID: <336a8c8b.875949@newshost>#1/1

On Fri, 02 May 1997 17:17:56 -0500, cfeder <cfeder_at_ctp.com> wrote:

>With Oracle 7.3, how does get a SQL statement to change rows into
>columns ?
>
>Here are some example tables:
>create table Person (idPerson, namePerson not null , primary key
>idPerson);
>create table PhoneUsage (idPhoneUsage,descrPhoneUsage not null, primary
>key idPhoneUsage);
>/* 1=Personal : 2=Business, 3=Fax, 4=Mobile, ect */
>create table Person_Phone( idPerson, Phone_number, idPhoneUsage
> , primary key ( idPerson, idPhoneUsage , Phone_number));
>
>The following view gets at syntax error with the nested select:
>
>create view PersonPhones
>(idPerson, namePerson, business_Phone, home_Phone,Fax_Phone) as
>SELECT P.idPerson, P.namePerson
>, (select C.Phone_number
> from Person_Phone C
> where C.idPerson = P.idPerson and C.idPhoneUsage = 1 )
>, (select C.Phone_number
> from Person_Phone C
> where C.idPerson = P.idPerson and C.idPhoneUsage = 2 )
>, (select C.Phone_number
> from Person_Phone C
> where C.idPerson = P.idPerson and C.idPhoneUsage = 3 )
>from Person P;
>
>Anyone have an alternative ?

decode.

create view personPhones
( idPerson, namePerson, business_phone, home_phone, fax_phone ) as
select p.idPerson, p.namePerson,

       max( decode( c.idPhoneUsage, 1, c.phone_Number, NULL ) ),
       max( decode( c.idPhoneUsage, 2, c.phone_Number, NULL ) ),
       max( decode( c.idPhoneUsage, 3, c.phone_Number, NULL ) )

from person p, person_phone c
where c.idPerson = p.idPerson
group by p.idPerson, p.NamePerson
/

The way this works is.....

select p.idPerson, p.namePerson,

       decode( c.idPhoneUsage, 1, c.phone_Number, NULL ),
       decode( c.idPhoneUsage, 2, c.phone_Number, NULL ),
       decode( c.idPhoneUsage, 3, c.phone_Number, NULL )

from person p, person_phone c
where c.idPerson = p.idPerson
/

would return a result set like:

1 TOM 1234 NULL NULL
1 TOM NULL 5678 NULL
1 TOM NULL NULL 9012
2 SUE 1234 NULL NULL
2 SUE NULL NULL 5678 The max + group by would 'squash' the above into:

1    TOM    1234  5678     8012
2    SUE    1234  NULL     6578

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Bethesda MD

http://govt.us.oracle.com/ -- downloadable utilities

---

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sat May 03 1997 - 00:00:00 CDT