Oracle SELECT query: collapsing NULL values when pairing up dates for different fields

Question

This question is very much like my previous question, but a bit more complicated. Rob van Wijk's answer worked perfectly for my other question, and I've been using that as a starting point. My problem now is that I am pivoting dates for different fields. Whereas before I cared about getting all open_in and open_out values for a given id, now I want new_in, new_out, open_in, open_out, fixed_in, and fixed_out for each id. I have the following:

SELECT id,
       state,
       state_time,
       MAX(new_row_num) OVER (PARTITION BY id ORDER BY state_time) AS new_row_group,
       MAX(open_row_num) OVER (PARTITION BY id ORDER BY state_time) AS open_row_group,
       MAX(fixed_row_num) OVER (PARTITION BY id ORDER BY state_time) AS fixed_row_group
FROM (
       SELECT id,
              state,
              state_time,
              CASE state
                   WHEN 'New'
                   THEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY state_time)
              END AS new_row_num,
              CASE state
                   WHEN 'Open'
                   THEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY state_time)
              END AS open_row_num,
              CASE state
                   WHEN 'Fixed'
                   THEN ROW_NUMBER() OVER (PARTITION BY id ORDER BY state_time)
              END AS fixed_row_num
       FROM ...
     )

This gives me data like the following:

id  state   state_time           new_row_group  open_row_group  fixed_row_group
1   New     2009-03-03 00:03:31  1
1   Closed  2009-03-04 04:15:27  1
2   New     2010-05-22 14:38:49  1
2   Open    2010-05-22 14:39:14  1              2
2   Fixed   2010-05-22 17:15:27  1              2               3

I would like data like the following:

id  new_in               new_out              open_in              open_out             fixed_in             fixed_out
1   2009-03-03 00:03:31  2009-03-04 04:15:27
2   2010-05-22 14:38:49  2010-05-22 14:39:14  2010-05-22 14:39:14  2010-05-22 17:15:27  2010-05-22 17:15:27

How can I pivot the data to get this date-pairing for each id?

Edit: to clarify, an id can enter and leave a state multiple times. For example, an id might go from New to Open to Fixed to Open to Fixed to Closed. In that case, there would need to be as many rows as is necessary to hold all the state times, e.g.:

id  new_in               new_out              open_in              open_out             fixed_in             fixed_out
4   2009-01-01 00:00:00  2009-01-02 00:00:00  2009-01-02 00:00:00  2009-01-03 00:00:00  2009-01-03 00:00:00  2009-01-04 00:00:00
4                                             2009-01-04 00:00:00  2009-01-05 00:00:00  2009-01-05 00:00:00  2009-01-06 00:00:00

Answer 1

I'm not sure how you'd prefer to handle the situation where the same state is repeated more than once for an ID. The following answer takes the easy route, assuming that you would want the first time the state was set and the last time the state was replaced.

select id, 
       min(case state when 'New' then state_time else null end) as new_in,
       max(case state when 'New' then out_state_time else null end) as new_out,
       min(case state when 'Open' then state_time else null end) as open_in,
       max(case state when 'Open' then out_state_time else null end) as open_out,
       min(case state when 'Fixed' then state_time else null end) as fixed_in,
       max(case state when 'Fixed' then out_state_time else null end) as fixed_out
from
    (select id, 
            state, 
            state_time, 
            lead(state_time) over (partition by id 
                                   order by state_time) as out_state_time
     from ...
    )
group by id

The lead analytic function gets the next row described by the partition/order statement, so that's the easiest way to find out when the state changed. The middle query is a basic pivot query (transforming columns to rows).

Answer 2

Sarah,

Here is an example with your sample data:

SQL> create table yourtable (id,state,state_time)
  2  as
  3  select 1, 'New', to_date('2009-03-03 00:03:31','yyyy-mm-dd hh24:mi:ss') from dual union all
  4  select 1, 'Closed', to_date('2009-03-04 04:15:27','yyyy-mm-dd hh24:mi:ss') from dual union all
  5  select 2, 'New', to_date('2010-05-22 14:38:49','yyyy-mm-dd hh24:mi:ss') from dual union all
  6  select 2, 'Open', to_date('2010-05-22 14:39:14','yyyy-mm-dd hh24:mi:ss') from dual union all
  7  select 2, 'Fixed', to_date('2010-05-22 17:15:27','yyyy-mm-dd hh24:mi:ss') from dual union all
  8  select 3, 'New', date '2009-01-01' from dual union all
  9  select 3, 'Open', date '2009-01-02' from dual union all
 10  select 3, 'Fixed', date '2009-01-03' from dual union all
 11  select 3, 'Open', date '2009-01-04' from dual union all
 12  select 3, 'Fixed', date '2009-01-05' from dual union all
 13  select 3, 'Closed', date '2009-01-06' from dual
 14  /

Table created.

The query:

SQL> select id
  2       , max(decode(state,'New',state_time))   new_in
  3       , max(decode(state,'New',out_time))     new_out
  4       , max(decode(state,'Open',state_time))  open_in
  5       , max(decode(state,'Open',out_time))    open_out
  6       , max(decode(state,'Fixed',state_time)) fixed_in
  7       , max(decode(state,'Fixed',out_time))   fixed_out
  8    from ( select id
  9                , state
 10                , state_time
 11                , max(cnt) over (partition by id order by state_time) the_row
 12                , lead(state_time) over (partition by id order by state_time) out_time
 13             from ( select id
 14                         , state
 15                         , state_time
 16                         , count(*) over (partition by id,state order by state_time) cnt
 17                      from yourtable
 18                  )
 19         )
 20   group by id
 21       , the_row
 22   order by id
 23       , the_row
 24  /

        ID NEW_IN              NEW_OUT             OPEN_IN             OPEN_OUT            FIXED_IN            FIXED_OUT
---------- ------------------- ------------------- ------------------- ------------------- ------------------- -------------------
         1 03-03-2009 00:03:31 04-03-2009 04:15:27
         2 22-05-2010 14:38:49 22-05-2010 14:39:14 22-05-2010 14:39:14 22-05-2010 17:15:27 22-05-2010 17:15:27
         3 01-01-2009 00:00:00 02-01-2009 00:00:00 02-01-2009 00:00:00 03-01-2009 00:00:00 03-01-2009 00:00:00 04-01-2009 00:00:00
         3                                         04-01-2009 00:00:00 05-01-2009 00:00:00 05-01-2009 00:00:00 06-01-2009 00:00:00

4 rows selected.

To understand how it works, execute the query from the inside out and check the intermediate result sets. Please let me know if you need some additional explanation.

Regards, Rob.