appending row number in sql select statement with row value

Question

I am trying to concatenate the select statment results in one row.
For eg : For this select statement output :

Name  
ABC  
DEF  
GHI

I needed following Output :
Name

1 ABC, 2 DEF, 3 GHI  

(Means row number should be appended in front for each row)
I am using wm_concat() function but it is giving me the following o/p : ABC,DEF,GHI
Can anyone help ?

Answer 1

It sounds like you want something similar to this. The listagg() function is available in Oracle 11g+:

select listagg(cast(rownum as varchar2(20))||' '|| name, ', ') 
        within group (order by name) name
from yourtable

See SQL Fiddle with Demo

Results:

|                NAME |
-----------------------
| 1 ABC, 2 DEF, 3 GHI |

If you do not have Oracle 11g, then you can use wm_concat():

select wm_concat(cast(rownum as varchar2(20))||' '|| name) name
from yourtable