how to obtain the functionality of LISTAGG function in Oracle 10g? I need to put output of a query in single line so that I can create a single tar file of all the files. But my database is running on 10g version. LISTAGG would have been very useful in this case. How to acheive same output as LISTAGG without using it

example:

SQL&gt; select col1 from table1;

col1

--------
file1

file2

file3


OUTPUT is required in following format,

col

--------
file1 file2 file3
I have prepared the following query to achieve this. Though there are number of ways.

SELCT col1 FROM table1;

COL1
-----
file1
file2
file3


SELECT 
   RTRIM (XMLAGG (XMLELEMENT (c, col1 || ',')).EXTRACT ('//TEXT()'), ',') col1
FROM table1;

COL1
-----------------
file1,file2,file3


OR

SELECT SUBSTR (SYS_CONNECT_BY_PATH (col1, ','), 2) col1
      FROM (SELECT col1 , ROW_NUMBER () OVER (ORDER BY col1 ) rn,
                   COUNT (*) OVER () cnt
              FROM table1)
     WHERE rn = cnt
START WITH rn = 1
CONNECT BY rn = PRIOR rn + 1;


Here is the dbfiddle with one more way to get the result.

Read more on String Aggregation Techniques