Advanced Search

Grouping function that concatenates strings

/ Published in: PL/SQL
Save to your folder(s)
See url for other techniques

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. FUNCTION f_concatinate_list (p_cursor IN sys_refcursor)
  2.       RETURN VARCHAR2(32787)
  3.    IS
  4.       v_return                  VARCHAR2(32787);
  5.       v_temp                    VARCHAR2(4000);
  6.    BEGIN
  7.       LOOP
  8.          FETCH p_cursor
  9.           INTO v_temp;
  10.  
  11.          EXIT WHEN p_cursor%NOTFOUND;
  12.          v_return := v_return || ',' || v_temp;
  13.       END LOOP;
  14.  
  15.       RETURN LTRIM (v_return, ',');
  16.    EXCEPTION
  17.       WHEN OTHERS
  18.       THEN
  19.          RAISE;
  20.    END f_concatinate_list;
  21.  
  22. -- CALLED THIS WAY:
  23. SELECT      a.Id,
  24.                    f_Concatinate_List
  25.                        (CURSOR (
  26.                                SELECT  TO_CHAR(b.strValue)
  27.                                FROM    table1 b
  28.                                WHERE   b.Id = a.Id
  29.                                )
  30.                        )                              AS concatenate_str
  31.        FROM        table1  a
  32.        WHERE       a.Id = 7374
  33.        GROUP BY    a.Id;

URL: http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.