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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]

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: [EMAIL PROTECTED] (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).

Reply via email to