Oracle SQL select statement that generates numbers 1-100 for a lookup table? -

i have query pulls aggregate stats age group

    agegroup    freq     0-5         2.3     6-10        3.2     11-15       3.6 

for various reasons, need output data lookup table every age 1-100 of following format

    age agegroup    freq     1   0-5         2.3     2   0-5         2.3     3   0-5         2.3     4   0-5         2.3     5   0-5         2.3     6   6-10        3.2     7   6-10        3.2     8   6-10        3.2     9   6-10        3.2     10  6-10        3.2 ... 

how go doing this? i'm not able create tables, i'm thinking if there's way write kind of select statement have ages 1-100 , agegroup , join original query has calculated frequencies agegroup - this

select t1.age, [case when statemenet assigns correct age group t1.age] "agegroup"  ([statemement generates numbers 1-100] "age") t1  join (original query creates aggreated agegroup data) t2 on t1.agegroup = t2.agegroup 

so have 2 questions

1. is approach makes sense @ all?

2. is possible generate t1 i'm looking for? i.e. select statement create t1 of form

   age agegroup 1   0-5 2   0-5 3   0-5 4   0-5   5   0-5  6   6-10 7   6-10 8   6-10 9   6-10 10  6-10  

   ...

that joined query has frequency agegroup?

something this... included age 0 (it can excluded if need be), , went through age 15. hard-coded; little work, can made match highest age in ranges.

this version unnecessary work, because computes substrings repeatedly. may still execute in less second, if performance becomes important, can written compute substrings in cte first, not computed repeatedly. (not shown here.)

with      inputs (agegroup, freq ) (        select '0-5',   2.3 dual union        select '6-10',  3.2 dual union        select '11-15', 3.6 dual      ) select c.age, i.agegroup, i.freq   (select level - 1 age dual connect level <= 16) c        inner join inputs        on age between to_number(substr(i.agegroup, 1, instr(i.agegroup, '-') - 1))               ,     to_number(substr(i.agegroup, instr(i.agegroup, '-') + 1)) order age ; 

output:

 age agegroup        freq ---- --------- ----------    0 0-5              2.3    1 0-5              2.3    2 0-5              2.3    3 0-5              2.3    4 0-5              2.3    5 0-5              2.3    6 6-10             3.2    7 6-10             3.2    8 6-10             3.2    9 6-10             3.2   10 6-10             3.2   11 11-15            3.6   12 11-15            3.6   13 11-15            3.6   14 11-15            3.6   15 11-15            3.6  16 rows selected.