plsql - Oracle pivot and sequencing with multiple columns -

plsql - Oracle pivot and sequencing with multiple columns -

i'm writing study (for sake of simplicity user-side) i'd aggregate info using oracle query imported excel, may run automatically printer well, processing needs done in query itself.

i'm using oracle, version 10.2.0.5.0 (select version v$instance).

ok, i'm using query;

select substr(arg_string,1,4) node, substr(numtodsinterval(end_time-start_time,'day'), 12, 8) dur, row_number() on (order substr(arg_string,1,4), start_time) rn pro.program_status prog_name ('%v8x-6%') , start_time > sysdate - 10;

which returns dataset resembling following;

node dur rn ---- -------- ---------- s002 00:25:40 1 s002 00:27:21 2 s002 00:03:20 3 s002 00:27:44 4 ... s004 00:21:29 22 s004 00:22:19 23 s004 00:05:13 24 s004 00:22:47 25 ... s005 00:03:13 42 s005 00:00:46 43 s005 00:02:01 44 s005 00:00:45 45 ... s152 00:01:13 1840

and desired output follows;

node dur1 dur2 dur3 ---- -------- -------- ---------- s002 00:25:40 00:27:21 00:03:20 s004 00:21:29 00:22:19 00:05:13 s005 00:03:13 00:00:46 00:02:01 , on

i have tried next query;

select node, max( case when rn = 1 dur else null end ) dur1, max( case when rn = 2 dur else null end ) dur2, max( case when rn = 3 dur else null end ) dur3 (select substr(arg_string,1,4) node, substr(numtodsinterval(end_time-start_time,'day'), 12, 8) dur, row_number() on (order substr(arg_string,1,4), start_time) rn pro.program_status prog_name ('%v8x-6%') , start_time > sysdate - 10) grouping node /

however returns next result 's002' labeled 1,2 , 3

node dur1 dur2 dur3 ---- -------- -------- -------- s002 00:25:40 00:27:21 00:03:20 s004 s005 s009 , on

i'm sure i'm missing obvious, after searching through site , numerous others cannot seem working properly.

a bit more detail, not know how many times each node appear or how many nodes there each day.

thanks in advance. tom

try:

select node, max( case when rn = 1 dur else null end ) dur1, max( case when rn = 2 dur else null end ) dur2, max( case when rn = 3 dur else null end ) dur3 (select substr(arg_string,1,4) node, substr(numtodsinterval(end_time-start_time,'day'), 12, 8) dur, row_number() on (partition substr(arg_string,1,4) order substr(arg_string,1,4), start_time) rn pro.program_status prog_name ('%v8x-6%') , start_time > sysdate - 10) grouping node

oracle plsql oracle11g