drop table t;
create table t(c number);
insert into t values(1);
insert into t values(2);
insert into t values(3);
insert into t values(5);
insert into t values(7);
insert into t values(8);
insert into t values(9);
insert into t values(11);
insert into t values(12);
insert into t values(15);
insert into t values(16);
insert into t values(20);
commit;
select min(c) start_range,max(c) end_range
from t
group by c-rownum
order by 1;
create table t(START_RANGE number, end_range number);
Insert into t (START_RANGE,END_RANGE) values (1,3);
Insert into T (START_RANGE,END_RANGE) values (5,5);
Insert into T (START_RANGE,END_RANGE) values (7,9);
Insert into T (START_RANGE,END_RANGE) values (11,12);
Insert into T (START_RANGE,END_RANGE) values (15,16);
Insert into T (START_RANGE,END_RANGE) values (20,20);
SELECT start_range,
end_range,
r-1 seq,
start_range+r-1 result
FROM t,
lateral (select rownum r
from dual
connect by level <= (end_range-start_range)+1);
select start_range,end_range,r
from t,
(select rownum r
from
dual connect by level <=(
select max(end_range)
from t)) d
where start_range <= r and end_range >=r;
Below Answers Received from subscribers.... Thanks for answering :-)
SELECT DISTINCT (LEVEL + START_RANGE)-1 NUM
FROM T
CONNECT BY (LEVEL +START_RANGE ) <= END_RANGE+1
ORDER BY NUM;
---------------------------------------------------------
create table cars (
name varchar2(100),
start_year number,
end_year number);
insert into cars values('Swift',2007,2009);
insert into cars values('Swift',2009,2011);
insert into cars values('Swift',2011,2013);
insert into cars values('Maruti',2009,2011);
insert into cars values('Maruti',2013,2015);
commit;
with r as (select distinct name,start_year+r year
from cars,
lateral(select rownum-1 r
from dual connect by level <=end_year-start_year+1)
order by 1,2)
select name,min(year) s_year,max(year) e_year
from r
group by name,year-rownum;
