Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: PL/SQL Question

RE: PL/SQL Question

From: Young, Jeff A. <jayoung_at_trilegiant.com>
Date: Tue, 19 Mar 2002 06:08:28 -0800
Message-ID: <F001.0042CF9D.20020319060828@fatcity.com>


You could use a user function. For example,

create or replace function lic_format (id in number) return varchar2 as
tmp varchar2(4000);
hold_tmp varchar2(50);
cursor c1 is
select name from software
where license_id = id;
begin
open c1;
loop
fetch c1 into hold_tmp;
exit when c1%notfound;
tmp := tmp ||hold_tmp||',';
end loop;
close c1;
return tmp;
end;
/

Then, your select would be:

select license_id, lic_format(license_id) from license;

The output would be:

LICENSE_ID FORMAT
---------- ---------

         1 abc,def,
         2 ghi,

Granted, this doesn't do the platform in parentheses, but it could if you beef up the function, and you may want to make the function smarter so that it didn't print out that last comma, but at least this gives you an idea.

-----Original Message-----
Sent: Tuesday, March 19, 2002 7:28 AM
To: Multiple recipients of list ORACLE-L

Hi all,

i have 2 tables software and licence. 1 licence can have many softwares.

softwares



name platform Licence_id
abc  NT        1
def         WIN2K                    1
ghi          all                             2

i want to write a query that displays the results as follows

licence_id softwares

-----------          -----------------------
1                        abc (NT), def(WIN2K)
2                         all

Any suggestions on how i can do this?

cheers!

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
  INET: iashraf_at_csc.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Young, Jeff A.
  INET: jayoung_at_trilegiant.com
Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Tue Mar 19 2002 - 08:08:28 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US