Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

I would like to return the results in one row, so like this:

Albania, Andorra, Antigua, ...
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?
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.
My question is based on the similar question on SQL server 2005.
UPDATE:
My function looks like this:
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;
         FETCH rec INTO field;
         EXIT WHEN rec%NOTFOUND;
         ret := ret || field || sep;
     END LOOP;
     if length(ret) = 0 then
          RETURN '';
          RETURN substr(ret,1,length(ret)-length(sep));
     end if;
The WM_CONCAT function (if included in your database, pre Oracle 11.2) or LISTAGG (starting Oracle 11.2) should do the trick nicely. For example, this gets a comma-delimited list of the table names in your schema:
select listagg(table_name, ', ') within group (order by table_name) 
  from user_tables;
select wm_concat(table_name) 
  from user_tables;
More details/options
Link to documentation  
                Note that Oracle don't recommend using WM_CONCAT as it is undocumented and unsupported: WMSYS.WM_CONCAT Should Not Be Used For Customer Applications, It Is An Internal Function (Doc ID 1336219.1)
– Burhan Ali
                Mar 24, 2014 at 10:22
                WM_CONCAT was dropped in 12c.  Anyone using this undocumented function is in for a surprise when they upgrade.
– Jon Heller
                May 9, 2014 at 6:24
Here is a simple way without stragg or creating a function.
create table countries ( country_name varchar2 (100));
insert into countries values ('Albania');
insert into countries values ('Andorra');
insert into countries values ('Antigua');
SELECT SUBSTR (SYS_CONNECT_BY_PATH (country_name , ','), 2) csv
      FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
                   COUNT (*) OVER () cnt
              FROM countries)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;
--------------------------
Albania,Andorra,Antigua                                                         
1 row selected.
As others have mentioned, if you are on 11g R2 or greater, you can now use listagg which is much simpler.
select listagg(country_name,', ') within group(order by country_name) csv
  from countries;
--------------------------
Albania, Andorra, Antigua
1 row selected.
                Nice short solution but a couple typos marred it.  This line should read:  FROM (SELECT country_name , ROW_NUMBER () OVER (ORDER BY country_name ) rn,
– Stew S
                Jan 26, 2009 at 21:26
                JoshL's suggestion of using the LISTAGG function is highly preferable for anyone using 11.2 or newer.
– JakeRobb
                Jun 8, 2015 at 21:10
                Just make sure your concatenated results don't exceed the VARCHAR2 max length limit of your oracle database (most likely 4000 bytes) otherwise you will run into ORA-01489 result of string concatenation is too long.
– JanM
                Jun 21, 2017 at 9:37
                @JanM [Comment 1 of 2] So, this is where I run into some challenges. I use REGEXP_REPLACE to remove the duplicates, but this does not work if I reach the VARCHAR2 max limit first.
– datalifenyc
                Jan 28, 2019 at 23:37
                @JanM [Comment 2 of 2] Next, I try to convert it to a clob using RTRIM,XMLAGG,XMLELEMENT, and GETCLOBVAL(), which I then cast back to VARCHAR2. However, the run time for the query turns into hours rather than 15 minutes. Do you have any recommendations of other approaches? Also, I saw a suggestion for creating a custom function instead.
– datalifenyc
                Jan 28, 2019 at 23:37
you can try this query.
select listagg(country_name,',') within group (order by country_name) cnt 
from countries; 
  2    from (
  3  select deptno,
  4         case when row_number() over (partition by deptno order by ename)=1
  5             then stragg(ename) over
  6                  (partition by deptno
  7                       order by ename
  8                         rows between unbounded preceding
  9                                  and unbounded following)
 10         end enames
 11    from emp
 12         )
 13   where enames is not null
Visit the site ask tom and search on 'stragg'  or 'string concatenation' . Lots of 
examples. There is also a not-documented oracle function to achieve your needs. 
                While it works, I dont recommend this solution to anybody. I saw an update command on table with only 80 000 rows using this solution and it was run for 6-8 hours.
– csadam
                Oct 24, 2011 at 16:06
                @csadam What do you recommend for larger rows, where the end goal is to remove duplicates to avoid the varchar2 4000 bytes limitation?
– datalifenyc
                Jan 29, 2019 at 3:11
                @myidealab You can find some workarounds here and here. To remove duplicates you can try an inner select with DISTINCT. Maybe the best solution is to create a custom function for these cases... However you could also redesign your solution, does it really have to use a 4000 char long string?
– csadam
                Jan 30, 2019 at 18:58
In this example we are creating a function to bring a comma delineated list of distinct line level AP invoice hold reasons into one field for header level query:
 FUNCTION getHoldReasonsByInvoiceId (p_InvoiceId IN NUMBER) RETURN VARCHAR2
  v_HoldReasons   VARCHAR2 (1000);
  v_Count         NUMBER := 0;
  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
     SELECT DISTINCT hold_reason
       FROM ap.AP_HOLDS_ALL APH
      WHERE status_flag NOT IN ('R') AND invoice_id = p2_InvoiceId;
BEGIN
  v_HoldReasons := ' ';
  FOR rHR IN v_HoldsCusror (p_InvoiceId)
     v_Count := v_COunt + 1;
     IF (v_Count = 1)
        v_HoldReasons := rHR.hold_reason;
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;
  RETURN v_HoldReasons;
I have always had to write some PL/SQL for this or I just concatenate a ',' to the field and copy into an editor and remove the CR from the list giving me the single line.
That is,
select country_name||', ' country from countries
A little bit long winded both ways.
If you look at Ask Tom you will see loads of possible solutions but they all revert to type declarations and/or PL/SQL
Ask Tom
SELECT REPLACE(REPLACE
((SELECT     TOP (100) PERCENT country_name + ', ' AS CountryName
FROM         country_name
ORDER BY country_name FOR XML PATH('')), 
'&<CountryName>', ''), '&<CountryName>', '') AS CountryNames
DECLARE @test NVARCHAR(max)
SELECT @test = COALESCE(@test + ',', '') + field2 FROM #test SELECT field2= @test
for detail and step by step explanation visit the following link

http://oops-solution.blogspot.com/2011/11/sql-server-convert-table-column-data.html