How to convert the Rows to Columns
I have Audit data as below
create table c_t (t_id varchar2(50), Change_Field varchar2(50),new_value varchar2(50), old_value varchar2(50), a_dt date);
insert into c_t values('T1','Status','WIP','Pending','01/08/2017 10:09:45');
insert into c_t values('T1','Status','Pending','WIP','01/08/2017 10:13:26');
insert into c_t values('T1','Status','WIP','Pending','01/08/2017 10:15:16');
insert into c_t values('T1','Status','Pending','WIP','01/08/2017 10:16:48');
COMMIT;
and need Output as
T_ID | P_start_dt | P_end_dt
T1 | 01/08/2017 10:09:45 | 01/08/2017 10:13:26
T1 | 01/08/2017 10:15:16 | 01/08/2017 10:16:48
But required output is not coming when uses PIVOT.
So when new_value equals:
* WIP -> start
* Pending -> end
?
In any case, Oracle Database adds an implicit group by including all table columns that don't appear in the pivot clause. So you need a subquery to select just the columns you want.
But if you select just new_value, a_dt and t_id you end up with just one row:
create table c_t (t_id varchar2(50), Change_Field varchar2(50),new_value varchar2(50), old_value varchar2(50), a_dt date);
alter session set nls_date_format = 'dd/mm/yyyy hh24:mi:ss';
insert into c_t values('T1','Status','WIP','Pending','01/08/2017 10:09:45');
insert into c_t values('T1','Status','Pending','WIP','01/08/2017 10:13:26');
insert into c_t values('T1','Status','WIP','Pending','01/08/2017 10:15:16');
insert into c_t values('T1','Status','Pending','WIP','01/08/2017 10:16:48');
COMMIT;
with rws as (
select t_id, a_dt, new_value
from c_t
)
select t_id, st, en
from rws
pivot (
min(a_dt) for new_value in ('WIP' as st, 'Pending' as en)
);
T_ID ST EN
T1 01/08/2017 10:09:45 01/08/2017 10:13:26
To split out each row for each new start, assign an incrementing number for each row for each pair of (t_id, new_value) values. You can use the row_number() analytic to do this:
with rws as (
select t_id, a_dt, new_value,
row_number() over (partition by t_id, new_value order by a_dt) rn
from c_t
)
select t_id, st, en
from rws
pivot (
min(a_dt) for new_value in ('WIP' as st, 'Pending' as en)
)
order by st;
T_ID ST EN
T1 01/08/2017 10:09:45 01/08/2017 10:13:26
T1 01/08/2017 10:15:16 01/08/2017 10:16:48
Of course, this assumes that all your rows go from WIP -> Pending -> WIP -> Pending. If this isn't the case you'll need to update the logic in the subquery. An exercise left for the reader ;)