Hi Tom,

Can you suggest me the solution for transposing the data from rows to columns, and to split the data into new column during particular interval.

Here is the sample tables and data that i am working on and the desired output as well:

I am using two tables as the below structure

CREATE TABLE TEST1 (
READING NUMBER, TIME VARCHAR2(50), VALUE VARCHAR2(50) );

CREATE TABLE TEST2 (
READING NUMBER, TIME1 VARCHAR2(50), VALUE1 VARCHAR2(50), TIME2 VARCHAR2(50), VALUE2 VARCHAR2(50), TIME3 VARCHAR2(50), VALUE3 VARCHAR2(50), TIME4 VARCHAR2(50), VALUE4 VARCHAR2(50), TIME5 VARCHAR2(50), VALUE5 VARCHAR2(50), TIME6 VARCHAR2(50), VALUE6 VARCHAR2(50), TIME7 VARCHAR2(50), VALUE7 VARCHAR2(50) );

And the data in test1 table is:

insert into TEST1 (READING, TIME, VALUE)
with somedata as
(
select 123, '08:30', '1.0' from dual
union all
select 123, '15:30', '2.0' from dual
union all
select 123, '23:30', '2.5' from dual
union all
select 123, '00:00', '3.0' from dual
union all
select 123, '10:30', '3.5' from dual
union all
select 123, '00:00', '4.0' from dual
union all
select 123, '14:30', '5.0' from dual
union all
select 123, '21:30', '6.0' from dual
)
select * from somedata ;

Finally, the desired output in TEST2 table have to look like:

READING TIME1 VALUE1 TIME2 VALUE2 TIME3 VALUE3 TIME4 VALUE4 TIME5 VALUE5 TIME6 VALUE6 TIME7 VALUE7
123 08:30 1.0 15:30 2.0 23:30 2.5
123 00:00 3.0 10:30 3.5
123 00:00 4.0 14:30 5.0 21:30 6.0

As i requested, the column have to be split into new line whenever the "Time" field has 00:00

Can you please help me with the solution for this.

create table test1
  (
    reading number, time varchar2 ( 50 ) , value varchar2 ( 50 )
  ) ;
insert into test1
    ( reading, time, value
    )
with somedata as
  ( select 123, '08:30', '1.0' from dual
  union all
  select 123, '15:30', '2.0' from dual
  union all
  select 123, '23:30', '2.5' from dual
  union all
  select 123, '00:00', '3.0' from dual
  union all
  select 123, '10:30', '3.5' from dual
  union all
  select 123, '00:00', '4.0' from dual
  union all
  select 123, '14:30', '5.0' from dual
  union all
  select 123, '21:30', '6.0' from dual
  )
select * from somedata ;

select *
from
  (select s.reading, time, value,
    dense_rank ( ) over ( order by lv ) g, 
    row_number ( ) over ( partition by lv order by value ) rn
  from
    (select s.*, last_value ( t ) ignore nulls over ( order by value ) lv
    from
      (select reading, time, value,
        case
          when time = '00:00'
          or rownum = 1 then rownum
        end t
      from test1
      ) s
    ) s
  ) pivot ( min ( time ) as time, min ( value ) as val 
    for ( rn ) in ( 1 as "1", 2 as "2", 3 as "3" ) )
order by g;

   READING          G 1_TIM 1_V 2_TIM 2_V 3_TIM 3_V
---------- ---------- ----- --- ----- --- ----- ---
       123          1 08:30 1.0 15:30 2.0 23:30 2.5
       123          2 00:00 3.0 10:30 3.5          
       123          3 00:00 4.0 14:30 5.0 21:30 6.0