Skip to Main Content

Breadcrumb

Question and Answer

Chris Saxon

Thanks for the question, Meka.

Asked: November 06, 2017 - 12:31 pm UTC

Last updated: November 06, 2017 - 1:46 pm UTC

Version: 11.2.0.4.0

Viewed 1000+ times

You Asked

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.

and Chris said...

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 ;)

Is this answer out of date? If it is, please let us know via a Comment

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.