Making pivot query on Oracle

Question

I want to make a pivot table for without using 'Pivot' function because of 10g doesn't support it.

I tried many ways however I failed a lot. Could you help me convert below query to pivot table that i specified below ?

select sysdate as SAMPLE_TIME,inst_id,wait_class,SUM(waits) as waits from (SELECT gv$system_event.inst_id,gv$system_event.wait_class, gv$system_event.time_waited as waits
        FROM gv$system_event where wait_class != 'Idle' union select inst_id, 'CPU' as wait_class, round(value/10000) as waits from gv$sys_time_model where stat_name = 'DB CPU') results
        where waits!=0
        group by wait_class,inst_id;

The result is like below;

  SAMPLE_TIME | INST_ID | WAIT_CLASS  | WAITS
---------------------------------------------
25/06/2017    2         User I/O    149719629
25/06/2017    1         User I/O    33314833
25/06/2017    2         System I/O  130276500
25/06/2017    1         System I/O  47508145

What i want is this;

SAMPLE_TIME | INST_ID | User I/O | System I/O
---------------------------------------------
25/06/2017      1       33314833    47508145
26/06/2017      2       149719629   130276500

krokodilko · Accepted Answer · 2017-06-24 21:16:54Z

1

Use SUM together with case expressions:

SELECT sysdate as SAMPLE_TIME,inst_id,
       SUM( CASE WHEN WAIT_CLASS = 'User I/O'
            THEN waits END ) As "User I/O",
       SUM( CASE WHEN WAIT_CLASS = 'System I/O'
            THEN waits END ) As "System I/O" 
FROM ( .....
.....
.....
)
GROUP BY sysdate, inst_id

answered Jun 24, 2017 at 21:16

krokodilko

36.3k7 gold badges62 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Making pivot query on Oracle

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related