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

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.