Hi All,
I am using Oracle database 11g for develop my application.
I am creating generic column report.
For this i have used PIVOT table concept.
My sample query is
with pivot_data as (
SELECT DEPTNO, JOB, SAL,MGR
FROM emp
)
SELECT *
FROM pivot_data
PIVOT (
SUM(SAL)
FOR DEPTNO
IN (&P90009000_LIST.)
)
this output is
JOB MGR 10 20 30
--------- ---------- ---------- ---------- ----------
PRESIDENT 5000
CLERK 7902 800
CLERK 7698 950
CLERK 7788 1100
CLERK 7782 1300
SALESMAN 7698 5600
MANAGER 7839 2450 2975 2850
ANALYST 7566 6000
8 rows selected.
but I need the output like below
JOB 10 20 30 MGR
--------- ---------- ---------- ---------- ----------
PRESIDENT 5000
CLERK 800 7902
CLERK 950 7698
CLERK 1100 7788
CLERK 1300 7782
SALESMAN 5600 7698
MANAGER 2450 2975 2850 7839
ANALYST 6000 7566
8 rows selected.
Here 10,20,30 columns are from pivot
How to do this in my query?
Thanks,
Su.gi