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

Question

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

User · Answer

SELECT REPLACE REPLACE   SELECT     TOP  100  PERCENT country name        AS CountryName FROM         country name ORDER BY country name FOR XML PATH          amp  lt CountryName gt           amp  lt CountryName gt        AS CountryNames

User · Answer

For Oracle you can use LISTAGG

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

you can try this query   select listagg country name      within group  order by country name  cnt  from countries

User · Answer

I needed a similar thing and found the following solution   select RTRIM XMLAGG XMLELEMENT e country name          EXTRACT    text          country name from

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

SELECT REPLACE REPLACE   SELECT     TOP  100  PERCENT country name        AS CountryName FROM         country name ORDER BY country name FOR XML PATH          amp  lt CountryName gt           amp  lt CountryName gt        AS CountryNames

User · Answer

You can use this as well   SELECT RTRIM             XMLAGG  XMLELEMENT  e  country name          EXTRACT     text                               country name   FROM countries

User · Answer

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

User · Answer

You can use this as well   SELECT RTRIM             XMLAGG  XMLELEMENT  e  country name          EXTRACT     text                               country name   FROM countries

User · Answer

I needed a similar thing and found the following solution   select RTRIM XMLAGG XMLELEMENT e country name          EXTRACT    text          country name from

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

you can try this query   select listagg country name      within group  order by country name  cnt  from countries

User · Answer

For Oracle you can use LISTAGG

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

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

Examples related to sql

Examples related to oracle

Examples related to concatenation

Examples related to pivot

Examples related to string-aggregation