Oracle: how to order LISTAGG() list

Question

Oracle 19c I am trying to write query result into .csv file including column names. In order to write column names as first line I am planning to loop over the result of this query:

select listagg(column_name,',') within group (order by column_name)
from user_tab_columns
where table_name = 'NS_INVE_ADJ'
order by column_id

Here are table columns id/name:

1   INVE_AUDIT_EXT_ID
2   TRANS_DATE
3   ADJ_LOCATION
4   ADJUSTMENT_ACCT
5   DIVISION_ID
6   ITEM
7   LOCATIONS
8   PROPERTY_MARK
9   REASON
10  ADJ_QTY
11  DIV_LINE

I noticed that result of the query above sorts it alphabetically, but I need it by column_id.

ADJUSTMENT_ACCT,ADJ_LOCATION,ADJ_QTY,DIVISION_ID,DIV_LINE,INVE_AUDIT_EXT_ID,ITEM,LOCATIONS,PROPERTY_MARK,REASON,TRANS_DATE

What am I missing?

Eugene

Answer 1

Simply sort it by id

select listagg("name",',') within group (order by "id")
from tab

LISTAGG("NAME",',')WITHINGROUP(ORDERBY"ID")
INVE_AUDIT_EXT_ID,TRANS_DATE,ADJ_LOCATION,ADJUSTMENT_ACCT,DIVISION_ID,ITEM,LOCATIONS,PROPERTY_MARK,REASON,ADJ_QTY,DIV_LINE

fiddle