CODEWITHSUNDEEP
sqloracle-database
Dimitri
Dimitri

Reputation: 453

Oracle SQL select individual values from range

Database has entries that look like this:

Title| Starting_Date | Ending_Date
 A   |    2012-03-03 | 2012-03-05
 B   |    2012-04-04 | 2012-04-04

What I want to get out of it is this:

Title| Starting_date | Ending_Date
   A | 2012-03-03    | 2012-03-03
   A | 2012-03-04    | 2012-03-04
   A | 2012-03-05    | 2012-03-05
   B | 2012-04-04    | 2012-04-04

Is there a way to do this without having to resort to creating a table that stores every possible day of the year and then doing comparison on it?

Upvotes: 0

Views: 113

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You just need a numbers table and you can generate that in a CTE:

with nums(n) as (
      select level - 1
      from dual
      connect by level <= 100
     )
select e.title, (e.starting_date + n.n) as starting_date, (e.starting_date + n.n) as ending_date
from entries e join
     nums n
     on n.n <= (ending_date - starting_date);

This assumes that a period of about 100 is big enough for your data.

Upvotes: 3

Related Questions