Pivot table column alignment

Su.gi

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

Su.gi

Thanks Solomon,

I have used the below code.

Its work fine.

with pivot_data as (

                                  SELECT DEPTNO, JOB, SAL,&P90009000_LIST., MGR

                                  FROM emp

                                  )

SELECT *

FROM pivot_data

PIVOT (

SUM(SAL)

FOR DEPTNO

IN (&P90009000_LIST.)

)

Thanks,

Su.gi

Solomon Yakobson

So what prevents you from listing columns in desired order:

SQL> with pivot_data as (
  2                      SELECT DEPTNO, JOB, SAL,MGR
  3                        FROM emp
  4                     )
  5  SELECT JOB,"10","20","30",MGR
  6  FROM pivot_data
  7  PIVOT (
  8  SUM(SAL)
  9  FOR DEPTNO
10  IN (10,20,30)
11  )
12  /

JOB               10         20         30        MGR
--------- ---------- ---------- ---------- ----------
CLERK                       800                  7902
PRESIDENT       5000
CLERK                                  950       7698
CLERK                      1100                  7788
CLERK           1300                             7782
SALESMAN                              5600       7698
MANAGER         2450       2975       2850       7839
ANALYST                    6000                  7566

8 rows selected.

SQL>

SY.

Su.gi

Thanks Solomon,

I have used the below code.

Its work fine.

with pivot_data as (

                                  SELECT DEPTNO, JOB, SAL,&P90009000_LIST., MGR

                                  FROM emp

                                  )

SELECT *

FROM pivot_data

PIVOT (

SUM(SAL)

FOR DEPTNO

IN (&P90009000_LIST.)

)

Thanks,

Su.gi