title: How can I combine multiple rows into a comma-delimited list in Oracle?
body: <p>I have a simple query:</p>
  
  <pre><code>select * from countries
  </code></pre>
  
  <p>with the following results:</p>
  
  <pre><code>country_name
  ------------
  Albania
  Andorra
  Antigua
  .....
  </code></pre>
  
  <p>I would like to return the results in one row, so like this:</p>
  
  <pre><code>Albania, Andorra, Antigua, ...
  </code></pre>
  
  <p>Of course, I can write a PL/SQL function to do the job (I already did in Oracle 10g), but is there a nicer, preferably non-Oracle-specific solution (or may be a built-in function) for this task?</p>
  
  <p>I would generally use it to avoid multiple rows in a sub-query, so if a person has more then one citizenship, I do not want her/him to be a duplicate in the list.</p>
  
  <p>My question is based on the similar question on <a href="https://stackoverflow.com/questions/180032/how-can-i-combine-multiple-rows-into-a-comma-delimited-list-in-sql-server-2005">SQL server 2005</a>.</p>
  
  <p><strong>UPDATE</strong>:
  My function looks like this:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION APPEND_FIELD (sqlstr in varchar2, sep in varchar2 ) return varchar2 is
  ret varchar2(4000) := '';
  TYPE cur_typ IS REF CURSOR;
  rec cur_typ;
  field varchar2(4000);
  begin
       OPEN rec FOR sqlstr;
       LOOP
           FETCH rec INTO field;
           EXIT WHEN rec%NOTFOUND;
           ret := ret || field || sep;
       END LOOP;
       if length(ret) = 0 then
            RETURN '';
       else
            RETURN substr(ret,1,length(ret)-length(sep));
       end if;
  end;
  </code></pre>