I am trying to use a select statement to create a view, transposing a table with datetime into a table with records in each row, the start-end time when the consecutive values by time (partition by station) in 'record' field is not 0.
Here is a sample of the initial table.
And how it should look like after transposing.
Can anyone help?
You can use the
conditional_change_event analytical function to create a special grouping identifier to split these out in a simple query:
select row_number() over () unique_id, station, min(datetime) startdate, max(datetime) enddate from ( select t.*, CONDITIONAL_CHANGE_EVENT(decode(recording,0,0,1)) over (partition by station order by datetime) chg from mytable t ) x where recording > 0 group by station, chg order by 1, 2
The decode is just to set up your islands and gaps (where gaps are recording <= 0 and islands are recording > 0). Then the change event on that will generate a new identifier for grouping. Also note that I am grouping on the change event even though it isn't part of the output.