From: "Michel Cadot" <micadot@netcourrier.com>
Subject: Re: Newbie : more than one ROW on one line
Date: 2000/08/08
Message-ID: <8mojf2$vrj$1@s1.read.news.oleane.net>#1/1
References: <8mk0uq$1sq5$1@nnrp01.ops.uunet.co.za>
X-MimeOLE: Produced By Microsoft MimeOLE V5.00.2314.1300
X-Complaints-To: abuse@oleane.net
X-Trace: s1.read.news.oleane.net 965726498 32627 195.101.229.231 (8 Aug 2000 09:21:38 GMT)
Organization: Guest of France Telecom Oleane's newsreading service
Reply-To: "Michel Cadot" <micadot@netcourrier.com>
NNTP-Posting-Date: Tue, 8 Aug 2000 09:21:38 +0000 (UTC)
Newsgroups: comp.databases.oracle.misc


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@usa.net> a écrit dans le message : 8mk0uq$1sq5$1@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.
>
>
>
>