Puddle Puddle - 1 year ago 141
SQL Question

Sort result from xmlagg(xmlelement(...)

I got this SQL query:

select rtrim(extract(xmlagg(xmlelement(e, column_name || ',')),
'/E/text()').getclobval(), ',') from all_tab_columns

I using this instead of LISTAGG(column_name, ',') because the result is going to exceed the limit of varchar2 (>4000).
Now I am asking myself whether it is possible to sort the result like LISTAGG does it.

So when having columns FERA, BAUT, CHECK_ID, ... I'd like them to be returned as: BAUT,CHECK_ID,FERA, ...

I am using Oracle Server and my framework doesn't allow me to work with PL/SQL.

Sud Sud
Answer Source

You can use a subquery and simply sort the columns before you pass it to the xml function. A simple solution.

 select rtrim(extract(xmlagg(xmlelement(e, column_name || ',')),
'/E/text()').getclobval(), ',') from 
(select * from  all_tab_columns
 where OWNER != 'TESTER' AND TABLE_NAME=upper('H4_POSIT')       
 order by COLUMN_NAME );
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download