Create dynamic column [duplicate]

Question

My table has this data

START_DATE            | STOP_DATE             | SAMPLE_DATE            | SAMPLE_VALUE
:-------------------- | :-------------------- | :--------------------- | -----------:
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:10:00 AM  | 10
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:14:00 AM  | 20
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:20:00 AM  | 15
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:40:00 AM  | 10
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:50:00 AM  | 21
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:55:00 AM  | 20

I want write query to return this result

START_DATE            | STOP_DATE             | part1 |part2 |part3 |part4
:-------------------- | :-------------------- | ------|------|------|-------
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 20    |15    |10    |20

I want start date to stop date split to 4 part [8:00-8:15,8:15-8:30,8:30-8:45,8:45-09:00] and set last sample value to the corresponding column.

Please help me

<>fiddle here

Answer 1

Here is one idea:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4 
from (
     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15 
                               order by sample_date desc) as rn
     from (
         select h.*, trunc(to_char (sample_date,'MI') / 15) * 15 as min15 
         from heats h
     ) T
) U
where rn = 1
group by start_date, stop_date;

In the innermost select, an attribute min15 is calculated. It is the 15 min period to which sample_date belongs

select h.*
     , trunc(to_char (sample_date,'MI') / 15) * 15 as min15 
from heats h

At the next level, we enumerate the rows within each 15 min period, starting with the last one:

     select start_date, stop_date, sample_value, min15
          , row_number() over (partition by min15 
                               order by sample_date desc) as rn
     from (
         ...
     )

At the outermost level, we filter out all but the last in each 15 min period (where rn = 1)

We also pivot out the last values to an individual column. Since we get null for all but one, we aggregate to a single row with max:

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4 
from (
    ...
)
where rn = 1
group by start_date, stop_date;

I don't normally use Oracle, so I googled for how to determine which 15 min period a row belongs to. There are probably more elegant ways to do it, but it should give you a start.

Fiddle