Creating a comma-separated list of items from a column

We needed to create a comma-separated list of items out of a column. We wanted “Red,Orange,Blue,Black,White”, and in the database we had

Red
Orange
Blue
Black
White

Grigoriy Novikov came up with a number of solutions:

Solution 1: The basic PL/SQL solution is to create a function with a loop and use it in your query:

CREATE OR REPLACE FUNCTION f_concat_tx RETURN VARCHAR2 AS
v_str VARCHAR2 (32767);
CURSOR cur1 IS
SELECT object_name
FROM all_objects
WHERE rownum < 25;

BEGIN
FOR c IN cur1 LOOP
v_str := v_str || c.object_name || ‘,’;
END LOOP;

v_str := RTRIM (v_str, ‘,’);
RETURN v_Str;
END;
/

SELECT f_concat_tx FROM DUAL;

Solution 2:  I love SQL and I like things simple, so I challenged him to find me an SQL function. Here are the other options:


SELECT LTRIM(MAX(SYS_CONNECT_BY_PATH(object_name, ',')), ',')
from ( SELECT rownum rn, object_name
FROM (SELECT object_name
FROM all_objects
WHERE rownum < 25
)
)
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1
/

Using SYS_CONNECT_BY_PATH is a bit tricky. It is valid only in hierarchical queries (queries with CONNECT BY). It returns the path of a column value from root to node, with column values separated by CHAR for each row returned by the CONNECT BY condition. In the query above, Grigoriy created the hierarchy by using the row number as a link.

One thing to know before using this query is that it will raise an error when a separator character is part of a value (ORA-30004: when using SYS_CONNECT_BY_PATH function, cannot have separator as part of column value).

Solution 3: Several solutions that use XML functionality will not raise an error when the separator is part of the value as shown here:


SELECT TO_CHAR(SYS_XMLAGG (
XMLELEMENT (OBJECT_NAME, OBJECT_NAME || ',')
).EXTRACT ('/ROWSET/OBJECT_NAME/text()').getclobval ())
FROM all_objects
WHERE rownum < 25
/

Solution 4:

SELECT TO_CHAR(SYS_XMLAGG(SYS_XMLGEN (
OBJECT_NAME || ',',
xmlgenformatType.createFormat (
'OBJECT_NAME'
)
)).EXTRACT ('/ROWSET/OBJECT_NAME/text()'
).getclobval ())
FROM all_objects
WHERE rownum < 25
/

You can find the documentation for of these functions in Oracle 11g here: XMLELEMENT, SYS_XMLAGG, SYS_XMLGEN. An extract follows:

XMLElement takes an element name, […] an optional collection of attributes for the element, and arguments that make up the content of the element. It returns an instance of type XMLType. XMLElement is similar to SYS_XMLGen except that XMLElement can include attributes in the XML returned, but it does not accept formatting using the XMLFormat object.

Solution 5:  In Oracle 11g Release 2, you can use the LISTAGG function. For a specified measure, LISTAGG orders data within each group specified in the ORDER BY clause and then concatenates the values of the measure column.

SELECT LISTAGG (OBJECT_NAME, ',') WITHIN GROUP (ORDER BY OBJECT_NAME ASC)
AS concatV
FROM all_objects
WHERE rownum < 25
/

Solution 6: CAUTION –  Possibly, the simplest option is WM_CONCAT. But this function is undocumented and unsupported by Oracle. Do not use it in production!!!

SELECT wm_concat (OBJECT_NAME) CONCAT
FROM all_objects
WHERE rownum < 25
/

…Hmmmm, sometimes the simplest solution is not really the easiest solution. These are not that simple and not that easy to maintain, but at least now you have a lot of options :).

As I finish writing this, Grigoriy just mentioned that we did not extensively cover all aspects. If you need more information, see Tom Kyte’s discussion.

Tagged with: , , , , , , ,
Posted in BLOG, Code tips, Oracle, PL/SQL, SQL
2 comments on “Creating a comma-separated list of items from a column
  1. Grigoriy Novikov says:

    Here is an update regarding undocumented function WM_CONCAT.
    Oracle may change the number and type of arguments, the return type from version to version and this is another reason why you should not use undocumented features.

    10.2.0.4 wm_concat returns varchar2
    10.2.0.5 wm_concat returns clob
    11.2.0.1 wm_concat returns varchar2
    11.2.0.2 wm_concat returns clob

    SQL> connect ***/***@***;

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    PL/SQL Release 11.2.0.1.0 – Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
    NLSRTL Version 11.2.0.1.0 – Production

    SQL> desc sys.wm_concat;
    FUNCTION sys.wm_concat RETURNS VARCHAR2
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    P1 VARCHAR2 IN

    SQL> connect ***/***@***;

    SQL> select * from v$version;

    BANNER
    ——————————————————————————–
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 – 64bit Production
    PL/SQL Release 11.2.0.2.0 – Production
    CORE 11.2.0.2.0 Production
    TNS for Linux: Version 11.2.0.2.0 – Production
    NLSRTL Version 11.2.0.2.0 – Production

    SQL> desc sys.wm_concat;
    FUNCTION sys.wm_concat RETURNS CLOB
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    P1 VARCHAR2 IN

    SQL> connect ***/***@***;

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> desc wmsys.wm_concat
    FUNCTION wmsys.wm_concat RETURNS CLOB
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    P1 VARCHAR2 IN

    SQL> connect ***/***@***;

    Connected to:
    Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – Production
    With the Partitioning, OLAP, Data Mining and Real Application Testing options

    SQL> desc wmsys.wm_concat
    FUNCTION wmsys.wm_concat RETURNS VARCHAR2
    Argument Name Type In/Out Default?
    —————————— ———————– —— ——–
    P1 VARCHAR2 IN

  2. Larry says:

    In this case you are describing a programmatic solution, however, if you simply have a list of items that you want to turn into a comma separated list once-only, you can paste the list into this online tool:
    http://convert.town/column-to-comma-separated-list

Leave a Reply

Your email address will not be published. Required fields are marked *

*

Disclaimer
The information presented on this blog is presented to provide general technical information. If, while attempting to apply any of the ideas, procedures, or suggestions herein, you experience any kind of programming or system problems or failure, it will be as a result of your own actions. Dulcian, Inc. and all authors of text found anywhere on this site, and all internally-linked Web sites, Mail Lists, Blogs and/or e-mail group discussion, disclaim responsibility for any user's actions and any damage that may occur based on information found on this website and associated Mail Lists, Blogs and/or e-mail group discussion. Any technical advice or directions found on or through this site is provided AS IS and its provided without warranty or any guarantee of its accuracy. You perform any modifications to programs or software AT YOUR OWN RISK.