2 Replies Latest reply: Dec 4, 2012 6:27 AM by Chanchal Wankhade RSS

    Simple SELECT statement or ?

    dba-india
      I have a table with email_address column.
      How can I generate a comma separated values of this email_addresses.
      Is there an easy way through SELECT statement or some other way? Please advise.

      email_address
      -------------------
      abcd1@company.com
      abcd2@company.com
      abcd3@company.com
      abcd4@company.com
      abcd5@company.com

      Desired output: abcd1@company.com,abcd2@company.com,abcd3@company.com,abcd4@company.com,abcd5@company.com
        • 1. Re: Simple SELECT statement or ?
          Purvesh K
          Provided you are on 11.2 or higher
          with data as
          (
            select 'abcd1@company.com' col from dual union all
            select 'abcd2@company.com' col from dual union all
            select 'abcd3@company.com' col from dual union all
            select 'abcd4@company.com' col from dual union all
            select 'abcd5@company.com' col from dual
          )
          select listagg(col, ',') within group(order by 1) col
            from data;
          
          COL
          -------------------------
          abcd1@company.com,abcd2@company.com,abcd3@company.com,abcd4@company.com,abcd5@company.com
          • 2. Re: Simple SELECT statement or ?
            Chanchal Wankhade
            Hi,

            You have one of option :-
            SQL> set colsep ,
            
            spool filename.csv
            
            select * from emp;
            
            spool off;
            it will generate csv file on the directory from which you have using oracle.