Search code examples
sqloracle-databasestring-aggregation

how to get one comma separated row from multiple rows in oracle without changing the order

I have a table which contains data as following:

Orange
Apple
PineApple
Grapes

The final output that I want is: Orange,Apple,PineApple,Grapes

I cannot use LISTAGG function because I do not want to change the order in which the data occurs.

How can this be achieved?

Solution

  • Use LISTAGG and order by ROWNUM to preserve an existing order:

    Oracle Setup:

    CREATE TABLE your_table ( value, order_index ) AS
SELECT 'Orange',    1 FROM DUAL UNION ALL
SELECT 'Apple',     2 FROM DUAL UNION ALL
SELECT 'PineApple', 3 FROM DUAL UNION ALL
SELECT 'Grapes',    4 FROM DUAL;

    Query:

    WITH pre_ordered_values ( value ) AS (
  SELECT value FROM your_table ORDER BY order_index
)
SELECT LISTAGG( value, ',' ) WITHIN GROUP ( ORDER BY ROWNUM ) AS aggregated_values
FROM   pre_ordered_values

    Output:

    | AGGREGATED_VALUES             |
| :---------------------------- |
| Orange,Apple,PineApple,Grapes |

    db<>fiddle here

    (Note: rows from a table are retrieved in a non-deterministic order and changing the data in the table, restoring a table from backups or enabling row-movement can change the order Oracle will return rows. If you require a ordering then this will have to be applied to the data rather than assuming an implicit order in which the rows will be generated.)