By rics


2009-01-22 12:51:39 8 Comments

I have a simple query:

select * from countries

with the following results:

country_name
------------
Albania
Andorra
Antigua
.....

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;
     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;

11 comments

@Daniel Emge 2009-01-22 20:24:40

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;

CSV                                                                             
--------------------------
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;

CSV                                                                             
--------------------------
Albania, Andorra, Antigua

1 row selected.

@Stew S 2009-01-26 21:26:39

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,

@JakeRobb 2015-06-08 21:10:39

JoshL's suggestion of using the LISTAGG function is highly preferable for anyone using 11.2 or newer.

@JanM 2017-06-21 09:37:26

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.

@myidealab 2019-01-28 23:37:29

@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.

@myidealab 2019-01-28 23:37:55

@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.

@Makatun 2011-08-23 17:21:22

For Oracle you can use LISTAGG

@rics 2011-08-25 08:27:13

In Oracle 11.2 as JoshL pointed out.

@A_T 2019-04-08 10:31:25

link is broken.

@JoshL 2011-07-27 17:12:51

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;

or

select wm_concat(table_name) 
  from user_tables;

More details/options

Link to documentation

@Kitet 2014-02-17 11:07:48

This command is faster than @Decci.7 has provided +1 and I like simple one-liners :D

@Burhan Ali 2014-03-24 10:22:29

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)

@Jon Heller 2014-05-09 06:24:52

WM_CONCAT was dropped in 12c. Anyone using this undocumented function is in for a surprise when they upgrade.

@gszecsenyi 2017-10-17 08:53:56

listagg is perfect,but the wm_concat not works with Oracle12

@Decci.7 2010-10-26 19:02:42

You can use this as well:

SELECT RTRIM (
          XMLAGG (XMLELEMENT (e, country_name || ',')).EXTRACT ('//text()'),
          ',')
          country_name
  FROM countries;

@styfle 2015-05-12 17:39:19

Thanks! This works in Oracle 10g.

@Gaya3 2013-03-15 13:14:31

you can try this query.

select listagg(country_name,',') within group (order by country_name) cnt 
from countries; 

@rics 2014-03-20 10:46:09

Listagg has been introduced in Oracle 11g Release 2.

@uniquephase 2016-11-17 18:56:23

this wouldn't work if there are too many columns.

@David MacIntosh 2012-11-16 22:09:05

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

  IS

  v_HoldReasons   VARCHAR2 (1000);

  v_Count         NUMBER := 0;

  CURSOR v_HoldsCusror (p2_InvoiceId IN NUMBER)
   IS
     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)
  LOOP
     v_Count := v_COunt + 1;

     IF (v_Count = 1)
     THEN
        v_HoldReasons := rHR.hold_reason;
     ELSE
        v_HoldReasons := v_HoldReasons || ', ' || rHR.hold_reason;
     END IF;
  END LOOP;

  RETURN v_HoldReasons;
END; 

@user1626874 2012-08-27 06:10:43

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

@Rashmi Kant 2011-11-04 12:04:17

you can use this query to do the above task

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

@tips 2010-11-26 06:24:16

I needed a similar thing and found the following solution.

select RTRIM(XMLAGG(XMLELEMENT(e,country_name || ',')).EXTRACT('//text()'),',') country_name from  

@csadam 2011-10-24 16:06:56

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.

@myidealab 2019-01-29 03:11:07

@csadam What do you recommend for larger rows, where the end goal is to remove duplicates to avoid the varchar2 4000 bytes limitation?

@csadam 2019-01-30 18:58:47

@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?

@Andrew Wood 2009-01-22 13:13:56

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

@tuinstoel 2009-01-22 13:10:05

The fastest way it is to use the Oracle collect function.

You can also do this:

select *
  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.

Related Questions

Sponsored Content

30 Answered Questions

[SOLVED] How do I concatenate two lists in Python?

45 Answered Questions

16 Answered Questions

[SOLVED] How can I do an UPDATE statement with JOIN in SQL?

28 Answered Questions

[SOLVED] How can I prevent SQL injection in PHP?

20 Answered Questions

[SOLVED] Get list of all tables in Oracle?

  • 2008-10-15 17:54:49
  • vitule
  • 2035850 View
  • 1021 Score
  • 20 Answer
  • Tags:   sql oracle

58 Answered Questions

[SOLVED] How can I concatenate two arrays in Java?

14 Answered Questions

[SOLVED] How do I limit the number of rows returned by an Oracle query after ordering?

17 Answered Questions

4 Answered Questions

[SOLVED] Inserting multiple rows in a single SQL query?

10 Answered Questions

[SOLVED] Can I concatenate multiple MySQL rows into one field?

Sponsored Content