Oracle-L: 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.

Jeff Young

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