Re: concatenation

From: Chris Halioris <halioris_chris_nonlilly_at_lilly.com>
Date: 1997/08/11
Message-ID: <33EF63BD.15AC_at_lilly.com>#1/1

Hagen wrote:
>
> I am hoping someone out there can help me with this problem.
> In an oracle database I would like to concatenate all the data from
> different entries in one field into one large entry.
>
> I have a a column called diary.notes which has many entries for each
> call it is related to. I am trying to take all the diary.notes for each
> call and concatenate them into one large entry. How can I do this.
>
> If you can help me please write to me at hagenj_at_mail.dec.com
>
> Thank You

Should be able to do this easily in PL/SQL. Where do you want to put the result? Do you want to store it in the database (in which case I would think you should just alter the database design), or do you want to do this at runtime for a specific call so you can display it on the screen in some front-end tool? Assuming you want the latter you could do the following (also assuming there is a column named call_id in the diary table and a column named call_date so you can order them chronologically).

PROCEDURE concatenate_notes(p_call_id IN NUMBER, p_text OUT VARCHAR2) IS

   CURSOR c_notes IS

      SELECT notes
        FROM diary
       WHERE call_id = p_call_id
      ORDER BY call_date;  

   l_text VARCHAR2(32000); -- or whatever length you need BEGIN
   l_text := null;
   FOR notesrec IN c_notes LOOP

• you may want to put some delimiter between these l_text := l_text || notesrec.notes END LOOP; p_text := l_text; END;

If I misunderstood the question (certainly possible), clarify and I'll see what I can do.

Chris Halioris Received on Mon Aug 11 1997 - 00:00:00 CEST