Re: Rows to Columns

From: Chrysalis <cellis_at_iol.ie>
Date: 1997/05/03
Message-ID: <336B3466.776C@iol.ie>#1/1

cfeder 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 ?

There are a number of options.

The simplest way with your existing table structure is, surely, to use the standard method of presenting data from multiple rows in a single row: viz. the join. Thus:

select p.idPerson ID, p.namePerson NAME

      ,c1.phone_Number PHONE_1, c2.phone_Number PHONE_2
      ,c3.phone_Number PHONE_3, c4.phone_Number PHONE_4
      ,... -- continue as necessary
from  person p
     ,person_phone c1, person_phone c2
     ,person_phone c3, person_phone c4
     ,...  -- continue as necessary
where p.idPerson = c1.idPerson (+) and c1.idPhoneUsage (+) = 1
 and  p.idPerson = c2.idPerson (+) and c2.idPhoneUsage (+) = 2
 and  p.idPerson = c3.idPerson (+) and c3.idPhoneUsage (+) = 3

 and p.idPerson = c4.idPerson (+) and c4.idPhoneUsage (+) = 4  and ... -- continue as necessary

This gives:
ID NAME PHONE_1 PHONE_2 PHONE_3 PHONE_4 -- -------------- ------- ------- ------- -------

 1 Tom            1234    2345            3456
 2 Dick           4567    5678    6789
 3 Harriet                        7890

 4 Joan    

Alternatively, it might be useful to produce a free-format list of phone numbers without leaving "holes" for non-existent numbers. A neat way to do this is to define a stored function which returns such a list.
The following produces a list in comma separated format as:

ID NAMEPERSON PHONE_LIST

-- --------------- --------------------------------
 1 Tom             1: 1234, 2: 2345, 4: 3456
 2 Dick            1: 4567, 2: 5678, 3: 6789
 3 Harriet         3: 7890

 4 Joan

create or replace function phone_list

   (persid IN number)
   return varchar2 as  

   cursor C1 (pid number) is

      select idPhoneUsage, phone_Number
      from   person_phone
      where  idPerson = pid
      order by idPhoneUsage;
       

   phlist varchar2 (500) := null;

begin

   for R1 in C1 (persid)
   loop

      phlist := phlist||R1.idPhoneUsage||': '
                      ||R1.phone_Number||', ';
   end loop;
       

   return rtrim(phlist,', '); -- remove final comma and space    

end phone_list;
/  

The SQL statement to produce the above output is then simply:

[column PHONE_LIST varchar2 (100); -- or whatever] select idPerson ID, namePerson NAME

      ,phone_list(idPerson) PHONE_LIST
 from person
[where ...]

Finally, unless there are pressing reasons for considering the phone number to be a separate entity, the simplest, as well as the most efficient method, is to re-define your person table to include a separate column for each phone number, its usage being determined by the column name.
This is impractical if you have an arbitrarily-large number of phone numbers for each person, but then you can't produce a columnar list anyway! (How wide is your paper/output device?)

Hope this helps.

Chrysalis. Received on Sat May 03 1997 - 00:00:00 CDT