Formatting number column in 11g Pivot function
705103Nov 2 2009 — edited Apr 19 2012Hello,
I have a question regarding formatting the data in a pivot table.
i have the pivot :
select * from
(
select
ENAME,
JOB,
sum(SAL) sal
from EMP
group by ename, job
)
pivot
(
sum(sal) as Salary for (JOB) in ('SALESMAN','MANAGER')
)
and the results :
-------------------------------------------------------------------------------
ENAME 'SALESMAN'_SALARY 'MANAGER'_SALARY
JONES - 2975
ALLEN 1600 -
FORD - - -
CLARK - 2450
MILLER - -
WARD 1250 -
How can i format the results of the Pivot to have the salary the format as with thousands ',' comma like : " select TO_CHAR(500000000000,'999G999G999G999G999G999G990','NLS_NUMERIC_CHARACTERS=.,') from dual" ?? Is this possible ?
i can't use the to_char() function is the Pivot because its not an aggregate function so TO_CHAR(SUM(..)) is not possible, and neigher SUM(TO_CHAR(..)) because its an error?
Please help.
Kind regards,
Alex