OraFAQ Forum: SQL & PL/SQL » Multiple column in Pivot Query

Home » SQL & PL/SQL » SQL & PL/SQL » Multiple column in Pivot Query (Oracle 11g R2, 11.2.0.1.0, RHEL 5.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Multiple column in Pivot Query [message #535886]

Sat, 17 December 2011 01:21

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Friends,

I've tried for pivot query feature of Oracle 11g, but I'm trying for pivot result on multiple column.
Herewith I'm displaying my try on single column pivot query.

SQL> select * from
  2  (select deptno,job,sal
  3  --,comm
  4  from emp)
  5  pivot (sum(sal) as payment for job in('CLERK','SALESMAN','MANAGER'))
  6  order by 1;

    DEPTNO 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- --------------- ------------------ -----------------
        10            1300                                 2450
        20            1900                                 2975
        30             950               5600              2850

I've tried this one also, but it didn't seems to be working.

SQL> select * from
  2  (select deptno,job,sal,comm
  3  from emp)
  4  pivot (sum(sal) as payment_sal,sum(comm) as payment_comm for job in('CLERK','SALESMAN','MANAGER'))
  5  order by 1;

    DEPTNO 'CLERK'_PAYMENT_SAL 'CLERK'_PAYMENT_COMM 'SALESMAN'_PAYMENT_SAL 'SALESMAN'_PAYMENT_COMM 'MANAGER'_PAYMENT_SAL 'MANAGER'_PAYMENT_COMM
---------- ------------------- -------------------- ---------------------- ----------------------- --------------------- ----------------------
        10                1300                                                                                      2450
        20                1900                                                                                      2975
        30                 950                                        5600                    2200                  2850

I want result like below.

    DEPTNO PAYMENT 'CLERK'_PAYMENT 'SALESMAN'_PAYMENT 'MANAGER'_PAYMENT
---------- ------- --------------- ------------------ -----------------
        10 SAL								1300                                 2450
        20 SAL								1900                                 2975
        30 SAL								 950               5600              2850
        30 COMM																	 2200

so please help me out,is it possible to have multiple column pivot query. please give examples/suggestions.

Thanks

Jimit

Report message to a moderator

Re: Multiple column in Pivot Query [message #535888 is a reply to message #535886]

Sat, 17 December 2011 02:48

transfer
Messages: 53
Registered: August 2007
Location: transfer

Member

Hi jimit,

Try UNPIVOT followed by PIVOT:

> SELECT * FROM (select deptno, job, sal, comm from EMP)
UNPIVOT(PAYMENT FOR COL IN(SAL, COMM))
PIVOT(SUM(PAYMENT) AS PAYMENT FOR JOB IN ('CLERK','SALESMAN','MANAGER'))
ORDER BY DEPTNO, COL;

DEPTNO                 COL  'CLERK'_PAYMENT        'SALESMAN'_PAYMENT     'MANAGER'_PAYMENT
---------------------- ---- ---------------------- ---------------------- -----------------
10                     SAL  1300                                          2450
20                     SAL  1900                                          2975
30                     COMM                        2200
30                     SAL  950                    5600                   2850

Best regards, Stew

Report message to a moderator

Re: Multiple column in Pivot Query [message #535889 is a reply to message #535888]

Sat, 17 December 2011 03:21

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Sashton,

Really a excellent solution, it works perfectly for me.I'll surely use this solution in production environment.It makes reporting much simpler.

Thanks a lot.

Regards

Jimit

Report message to a moderator

Previous Topic:	index effect while inserting records in a table
Next Topic:	Error while inserting image into database

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sat Sep 06 13:18:54 CDT 2025