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