0

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> select col1 from table1;

col1

--------
file1

file2

file3

OUTPUT is required in following format,

col

--------
file1 file2 file3
2

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.