Oracle SQL select statement that generates numbers 1-100 for a lookup table?
<p>I have a query that pulls some aggregate stats by age group</p>

<pre><code>    Agegroup    Freq
    0-5         2.3
    6-10        3.2
    11-15       3.6
</code></pre>

<p>For various reasons, I need the output data to be a lookup table for every age 1-100 of the following format</p>

<pre><code>    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
...
</code></pre>

<p>How could I go about doing this? I'm not able to create tables, so I'm thinking if there's a way to write some kind of select statement that will have all ages 1-100 and the agegroup and then join it to the original query which has the calculated frequencies by agegroup - something like this</p>

<pre><code>SELECT t1.age, [case when statemenet that assigns the correct age group from t1.Age] "Agegroup"

FROM ([statemement that generates numbers 1-100] "age") t1

JOIN (Original query that creates the aggreated agegroup data) t2 on t1.Agegroup = t2.Agegroup
</code></pre>

<p>So I have two questions</p>

<ol>
<li>Is this an approach that makes sense at all?</li>
<li><p>Is it possible to generate the t1 I'm looking for? I.e. a select statement that will create a t1 of the form</p>

<pre><code>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 
</code></pre>

<p>...</p></li>
</ol>

<p>that could then be joined with the query that has the frequency by agegroup?</p>