What I’m trying to do is take a record that looks like this:
Start_DT End_DT ID 4/5/2013 4/9/2013 1
and change it to look like this:
DT ID 4/5/2013 1 4/6/2013 1 4/7/2013 1 4/8/2013 1 4/9/2013 1
it can be done in Python but I am not sure if it is possible with SQL Oracle? I am having difficult time making this work. Any help would be appreciated.
Thanks
Advertisement
Answer
connect by level is useful for these problems. suppose the first CTE named “table_DT” is your table name so you can use the select statement after that.
with table_DT as ( select to_date('4/5/2013','mm/dd/yyyy') as Start_DT, to_date('4/9/2013', 'mm/dd/yyyy') as End_DT, 1 as ID from dual ) select Start_DT + (level-1) as DT, ID from table_DT connect by level <= End_DT - Start_DT +1 ;