RE: Simple SQL question

From: Diana Duncan <Diana_at_fileFRENZY.com>
Date: Wed, 27 Sep 2000 17:29:26 -0400
Message-Id: <10632.118081@fatcity.com>

And if you absolutely must be able to get it in SQL, write your own function:

create or replace function fruit_list (v_Name in varchar2) return varchar2 is

	cursor get_fruits is
		select fruit
		from people_fruits
		where name = v_Name;
	fruitCur	get_fruits%rowtype;
	str	varchar2(500);
begin
	for fruitCur in get_fruits loop
		if get_fruits%rowcount = 1 then
			str := str || ', ';
		end if;
		str := str || fruitCur.fruit;
	end loop;
	return str;

end fruit_list;
/

select distinct name, fruit_list(name)
from people_fruits;

Sorry for any syntax errors, off the top o' my head.

Diana

-----Original Message-----
From: Steven Monaghan [mailto:MonaghaS_at_mscdirect.com] Sent: Wednesday, September 27, 2000 6:07 PM To: Multiple recipients of list ORACLE-L Subject: RE: Simple SQL question

You can't in SQL to my knowledge, but it's pretty easy in PL/SQL  

declare
 cursor c1 is select distinct name from people_fruits; begin
 for rec1 in c1 loop
  declare
   cursor c2 is select fruit from people_fruits where name = rec1.name;    v1 varchar2(255);
  begin
   v1 := rec1.name||' : ';
   for rec2 in c2 loop
    v1 := v1||rec2.fruit||', ';
   end loop;
  end;
  dbms_output.put_line(v1);
  v1 := null;
 end loop;
end;
/
 

You'll need to check the syntax, I did this off of the top of my head.  

Steve

---

Steven Monaghan
Oracle DBA
MSC Industrial Direct Co., Inc.
Melville, NY
MonaghaS_at_mscdirect.com

---

-----Original Message-----
Sent: Wednesday, September 27, 2000 4:19 PM To: ORACLE-L_at_lists.sunysb.edu; ORACLE-L_at_fatcity.com; oracledba_at_quickdoc.co.uk

HI !  

It's too late and i can't find a simple solution for the following :  

Let's say a have a table PEOPLE_FRUITS ( NAME , FRUIT ) with the following 5 rows :
NAME FRUIT
===== =======

John       Apple 
Mike      Orange 
John      Pineapple
John      Peach
Mike      Apple
 

I want to write an SQL statement to output : John : Apple , Pineapple , Peach
Mike : Orange , Apple .  

How can i do this ?  

TIA   Andrey .    

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Steven Monaghan
  INET: MonaghaS_at_mscdirect.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

Received on Wed Sep 27 2000 - 16:29:26 CDT