How to Combine Multiple Rows into a Comma-Delimited List in Oracle

How can I combine multiple rows into a comma-delimited list in Oracle?

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.

Multiple row data in single row as comma separated (Oracle)

This is what LISTAGG was built for

  SELECT t.USER, t.group, LISTAGG (t.sub, ',') WITHIN GROUP (ORDER BY t.pk_id)
    FROM your_table t
GROUP BY t.USER, t.GROUP

Oracle : Combine unique row values into comma-separated string

Just pre-filter your rows with a common table expression, then do your string concatenation.

with cte1 as (
    select distinct *
    from tblData
    where v_data is not null
)
select g_name,
    g_id,
    RTRIM(XMLAGG(XMLELEMENT(e, v_data || ',')).EXTRACT('//text()'), ',')
from cte1
group by g_name, g_id

Transform SQL Rows into Comma-Separated values in Oracle

http://sqlfiddle.com/#!4/9ad65/2

select 
  listagg(name, ',') 
    within group (order by id) as list 
from student

Oracle: Combine multiple results in a subquery into a single comma-separated value

There is an excellent summary of the available string aggregation techniques on Tim Hall's site.

SQL - Multiple Values comma separated when using GROUP BY

This link refers to a number of examples of different ways to do this on Oracle. See if there's something there that you have permissions on your database to do.

How can I combine multiple rows into a comma-delimited list in SQL Server 2005?

DECLARE @XYList varchar(MAX)
SET @XYList = ''

SELECT @XYList = @XYList + CONVERT(varchar, X) + ',' + CONVERT(varchar, Y) + ','
FROM POINTS

-- Remove last comma
SELECT LEFT(@XYList, LEN(@XYList) - 1)

---

Related Topics