Re: get multiple rows to columns

kenneth <kenneth_at_hotmail.com> wrote in message news:<b931bu44omsev09t507ktm5pjsgb9err8a_at_news-west.newscene.com>...
> 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
>
>
 

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

I would not have guessed. Your English seems good.

Consider the following changes. They should give you ideas on getting rid of the temp table, but I'll leave the temp table for now.

 (that table is more of a staging area, maybe worth keeping.)

> CREATE OR REPLACE PROCEDURE COLL_EMP2 IS
>

previous_id varchar2(10);
list varchar2(300); --- size chosen to match your temp table

> begin
>
> delete from tora_coll_emp;
 

>
>

previous_id := '0';
list := '';

> for m_rec in (select last_name,job_id from employees

                order by job_id
                ) loop

if (m_rec.job_id = previous_id ) then

• append the next item onto the list list := list||m_rec.last_name||', '; else
• we are on the next job, record the previous one:
• delete that last trailing comma(,) before list := substr(list,1,length(list)-2);

   insert into tora_coll_emp values (previous_id, list);

   previous_id := m_rec.job_id;
   list := list||m_rec.last_name||', ';
end if;    

>
> end loop;
>
> commit;
>
>
> end coll_emp2;

I didn't compile it, but I think you can get pase any syntax errors I have.
Think about it.

Ed Received on Mon Apr 08 2002 - 23:48:04 CEST