Column
ABC
DEF
GHI

The expected result is in a single row with bullet numbers as separation:

1. ABC 2. DEF 3. GHI

CodePudding user response：

You can't do it all within listagg, but you can generate the sequential number:

select your_column, row_number() over (order by your_column) as rn
from your_table

and then use that as an inline view, and concatenate the values together in the listagg:

select listagg(rn || '. ' || your_column, ' ') within group (order by rn) as result
from (
  select your_column, row_number() over (order by your_column) as rn
  from your_table
)

RESULT
--------------------
1. ABC 2. DEF 3. GHI

db<>fiddle

I've assumed you want them in alphabetic order; if not you can adjust the row_number() call.

I tried to use rownum in listtag

That would work too, sort of:

select listagg(rownum || '. ' || your_column, ' ') within group (order by rownum) as result
from your_table

... but you don't seem to be able to control the ordering, unless you use a subquery anyway - db<>fiddle