Hi All,
I have a query which uses listag function to combine multiple values separated by comma. then i port the values in spool file but it is not correctly writing it.
Query :
Select dept_id , listag(emp_id ,',') within group order by emp_id from employee group by dept_id ;
Output :
Dept_id Emp_id
1 123,456,768
2 988,876,543
I need to write it in spool
My Query:
set serverouput on
set verify off;
spool '/Path/test_1.csv' ;
prompt "Dep,Emp"
select
'"'
"Dep"
||'","'
|| Emp
'"' as Output from
(
with Q as
(Select dept_id , listag(emp_id ,',') within group order by emp_id as emp from employee group by dept_id )
select dept_id as "Dep",emp as "Emp" from Q);
spool off;
Exit ;
/
When i executed my Query and retrieve the output
Notepad :
Dep , Emp
"1" , "123,456,768"
"2" , "988,876,543"
In excel :
Dep , Emp
"1" , "1,23,456,768"
"2" , "9,88,876,543" ;
Why it taking it as number within the value while in Notepad it shows correctly.
Please kind your advice.
Regards,
Uday