Re: 'SUM' strings to concatenated string

From: Stronfogg <philmulhall_at_gmail.com>
Date: 19 Feb 2006 01:11:00 -0800
Message-ID: <1140340260.499456.296290@o13g2000cwo.googlegroups.com>

Nothing like a little pl/sql to solve this one. Note there are a couple of variations that can be made depending on what your specific needs are...

SQL> set serveroutput on
SQL> variable v_out_str varchar2(200);
SQL> get concateg
  1  declare
  2     -- declare the cursor...
  3     cursor c1 is (select username from table_users);
  4  begin
  5     for c1_rec in c1
  6     loop
  7      if (c1%rowcount = 1) -- need this to position first comma
correctly
  8       then
  9        :v_out_str := c1_rec.username;
 10      else
 11        :v_out_str := :v_out_str || ',' || c1_rec.username;
 12      end if;
 13     end loop;

 14* end;
 15 /

PL/SQL procedure successfully completed.

SQL> print

V_OUT_STR

---

Miller,Smith,Frankfurter,Gonzales

SQL>
SQL> select * from v$version;

BANNER

---

Oracle9i Enterprise Edition Release 9.2.0.7.0 - Production PL/SQL Release 9.2.0.7.0 - Production
CORE 9.2.0.7.0 Production
TNS for 32-bit Windows: Version 9.2.0.7.0 - Production NLSRTL Version 9.2.0.7.0 - Production

SQL> HTH
Phil. Received on Sun Feb 19 2006 - 03:11:00 CST