Search code examples
sqloraclewindow-functions

How can I create age band from different rows in oracle

I have a table like this

rateId  gender  Age
HV1     M       0
HV1     M      12
HV1     M      50
HV1     F      0
HV1     F      20
HV1     F      50

I want to create an age band for all rateId per gender. When there is no upper age anymore, it can be 100. So my result has to be:

rateId  gender  AgeLow  AgeHigh
HV1     M       0       12
HV1     M       12      50
HV1     M       50      100
HV1     F       0       20
HV1     F       20      50
HV1     F       50      100

I think it could be possible with a partition by or something like that, but I don't find how to use it.

Solution

  • You can try to use LEAD window function.

    SELECT  t1.rateId, 
        t1.gender,  
        t1.Age AgeLow,
       LEAD(t1.Age,1,100) over(partition by t1.rateId,t1.gender order by t1.Age) AgeHigh
FROM T t1

    SQL DEMO