Re: Newbie : more than one ROW on one line

From: Michel Cadot <micadot_at_netcourrier.com>
Date: 2000/08/08
Message-ID: <8mojf2$vrj$1@s1.read.news.oleane.net>#1/1

Here's two ways.

If you know all the phone types, you can use:

select name.surnme,

       phone1.phone_no "Ext",
       phone2.phone_no "Home",
       phone3.phone_no "Cell"
from Phone_Table phone3,
     Phone_Table phone2,
     Phone_Table phone1,
     Name_Table name
where phone3.id (+) = name.id
  and phone3.phone_type = 'Ext'
  and phone2.id (+) = name.id
  and phone2.phone_type = 'Home'

  and phone1.id (+) = name.id
  and phone1.phone_type = 'Cell'
order by 1
/

If you don't, you can create a function:

create function phone (id phone_table.id%type) return varchar2 is

   cursor curs is

      select phone_type, phone_no
      from phone_table
      where phone_table.id = id
      order by phone_type;

   result varchar2(2000) := '';
begin

   for rec in curs loop

       result := result || ' ' ||
                 rec.phone_type || ':' || rec.phone_no;

   end loop;
   return result;
end;
/

select surnme, phone(id) from name_table order by 1
/

--
Have a nice day
Michel


Walter W <clothahump_at_usa.net> a écrit dans le message : 8mk0uq$1sq5$1_at_nnrp01.ops.uunet.co.za...


> Hi there

>

> Is there an elegant way - not multiple unions ...

> in  which I can display data from parent table, and one of it's chilren on

> one line.

>

> ie:

> Name_Table

> ID

> Surnme

> FirstNme

>

> PhoneTable

> ID

> Phone_Type

> Phone_No

>

> Where I want to see output like...

>

> Scott   Ext:126   Home:903-3342  Cell:201-9913

> Smith   Ext:127   Home:904-5542  Cell:111-9443

>

> Currently I'm doing two simple selects, and building the data on w/s.

> But I would like to do it in a "better" way - more logic on central server.

>

> Any answers would be appreciated.

>

>

>

>

Received on Tue Aug 08 2000 - 00:00:00 CDT