Forum Stats

  • 3,851,702 Users
  • 2,264,013 Discussions
  • 7,904,820 Comments

Discussions

Pivot table column alignment

Su.gi
Su.gi Member Posts: 511
edited Apr 17, 2015 9:28AM in SQL & PL/SQL

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

Tagged:

Best Answer

  • Su.gi
    Su.gi Member Posts: 511
    edited Apr 17, 2015 9:28AM Answer ✓

    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

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,721 Red Diamond
    edited Apr 16, 2015 9:58AM

    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
  • Su.gi
    Su.gi Member Posts: 511
    edited Apr 17, 2015 9:28AM Answer ✓

    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

This discussion has been closed.