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 ?
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
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
Additionaly you could use WITH clause:
WITH tmp AS
(
:your_select_statement
)
SELECT
LISTAGG(rownum || ' ' || :your_column, ', ')
WITHIN GROUP (ORDER BY :your_column) tmp
FROM tmp;
Then rownum is from tmp temporary table which have results of :your_select_statement not from primary table.