Re: get multiple rows to columns

Thanks for the (short) answer.

did some studying on cursors and well here's my first approach:

create table tora_coll_emp (job_id varchar2(10),members varchar2(300));

CREATE OR REPLACE PROCEDURE COLL_EMP2 IS begin

delete from tora_coll_emp;
insert into tora_coll_emp (job_id) select job_id from jobs;

for m_rec in (select last_name,job_id from employees) loop                  

	update tora_coll_emp
	set members=members||', '||m_rec.last_name
	where job_id=m_rec.job_id
		  	AND members is not null;
	
	update tora_coll_emp
	set members=m_rec.last_name
	where job_id=m_rec.job_id
		  	AND members is null;
	

end loop;         

commit;         

end coll_emp2;

Question:
is there a better way to differ the first update where members is null from the following where I'm adding further items, will the performance be better with a different approach?

PS: I won't use a temp table in the real code;

thanks

for any help

kenneth

On 25 Mar 2002 14:37:24 -0800, ed.prochak_at_alltel.com (Ed prochak) wrote:

>Looks like you answered your own question: IN A STORED PROCEDURE.
>(hint: look up cursors in PL/SQL)
>
>kenneth <kenneth_at_hotmail.com> wrote in message news:<phfr9u4j9fn36ikmac2q0udhsvgo7emqro_at_news-west.newscene.com>...
>> Hi,
>> I'm new to Oracle
>>
>> I have the following problem to solve in a store procedure that passes
>> its output to a crystal report.
>> It's probably a generic problem
>>
>> I have table 1 containing patients surgery data and table 2 the drugs
>> used. The key they are joined is the surgeryid.
>> The drug table has one line for every drug used. For one surgeryid I
>> can get up to 9 records.
>> If I just join on the surgeryid I get every surgery case more than
>> once, one for every drug used.
>>
>> the output I get now
>> patient 1 surgery therapy drug1
>> patient 1 surgery therapy drug2
>> patient 1 surgery therapy drug3
>> patient 2 surgery therapy drug1
>> patient 2 surgery therapy drug2
>>
>> .....
>>
>> but I want to have an output that looks like this
>>
>> patient1 surgery, therapy, drug1,drug2,drug3
>> patient2.....
>>
>>
>> of course if there's just one drug used I don't want to have
>> "drug1,,,,,, " in the report etc.
>>
>> system used is oracle 8.0.5
>>
>> How do I get the multiple sets in table 2 in one column (or will I
>> need up to 9) in the temp table used in the report.
>>
>>
>>
>>
>> thanks
>>
>> kenneth
>>
>> PS:
>> sql and english are not my native languages :-))
  Received on Sun Apr 07 2002 - 20:25:13 CEST