Need to group values in SQL

Question

Below is my table data structure.

select 100 id,  1   srno,0 amt from dual
union all
select 100 id,  2   srno,       1000 amt from dual
union all
select 100 id,  3   srno,       1000 amt from dual
union all
select 100 id,  4    srno,      0 amt from dual
union all
select 100 id,  5   srno,       2000 amt from dual

I want result like this,

ID   From_Srno     To_Srno     amt
100   1               1         0
100   2               3         1000
100   4               4         0
100   5               5         2000

Thanks, Fame

Answer 1

This reads as a gaps-and-island problem, where you want to group together "adjacent" rows that have the same amt.

I would recommend the difference between row numbers to define the groups:

select id, min(srno) from_srno, max(srno) max_srno, amt
from (
    select t.*, 
        row_number() over(partition by id order by srno) rn1,
        row_number() over(partition by id, amt order by srno) rn2
    from mytable t
) t
group by id, amt, rn1 - rn2

Demo on DB Fiddle:

 ID | FROM_SRNO | MAX_SRNO |  AMT
--: | --------: | -------: | ---:
100 |         1 |        1 |    0
100 |         2 |        3 | 1000
100 |         4 |        4 |    0
100 |         5 |        5 | 2000