Posted By

pollux on 03/27/09


Tagged

db


Versions (?)

Grouping function that concatenates strings


 / Published in: PL/SQL
 

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

See url for other techniques

  1. FUNCTION f_concatinate_list (p_cursor IN sys_refcursor)
  2. v_return VARCHAR2(32787);
  3. v_temp VARCHAR2(4000);
  4. FETCH p_cursor
  5. INTO v_temp;
  6.  
  7. EXIT WHEN p_cursor%NOTFOUND;
  8. v_return := v_return || ',' || v_temp;
  9.  
  10. RETURN LTRIM (v_return, ',');
  11. END f_concatinate_list;
  12.  
  13. -- CALLED THIS WAY:
  14. SELECT a.Id,
  15. f_Concatinate_List
  16. (CURSOR (
  17. SELECT TO_CHAR(b.strValue)
  18. FROM table1 b
  19. WHERE b.Id = a.Id
  20. )
  21. ) AS concatenate_str
  22. FROM table1 a
  23. WHERE a.Id = 7374
  24. GROUP BY a.Id;

Report this snippet  

You need to login to post a comment.