Expanding a query's result set (or using an intermediate table)?

As a follow-up to my previous question, there is an alternative that I would like to explore. My table layout is as follows:

T1:  ID  |  Date  |  Hour  | Interval

The intervals span from 1 to 12 (the 5-minute interval in the hour), the hour goes from 1 to 24, and the date/ID is arbitrary. When I am pulling the data from an external source, there are some rows that have an interval of 0. When there is a 0 for the interval, this indicates that the entire hour is covered (i.e. all intervals from 1 to 12).

Example data:

T1:  1  |  1/1/2011  |  1  |  1
     1  |  1/1/2011  |  1  |  4
     1  |  1/1/2011  |  1  |  0

     1  |  1/1/2011  |  2  |  2
     1  |  1/1/2011  |  2  |  5

     1  |  1/1/2011  |  3  |  0

Notice that there is an overlap of the intervals and a 0-flagged interval. Is there any way I can expand the 0 intervals into all 12 intervals for the hour? So, I basically want to transform the example data into:

T1:  1  |  1/1/2011  |  1  |  1
    开发者_StackOverflow 1  |  1/1/2011  |  1  |  2
     1  |  1/1/2011  |  1  |  3
     1  |  1/1/2011  |  1  |  4
     ...
     1  |  1/1/2011  |  1  |  11
     1  |  1/1/2011  |  1  |  12

     1  |  1/1/2011  |  2  |  2
     1  |  1/1/2011  |  2  |  5

     1  |  1/1/2011  |  3  |  1
     1  |  1/1/2011  |  3  |  2
     1  |  1/1/2011  |  3  |  3
     ...
     1  |  1/1/2011  |  3  |  11
     1  |  1/1/2011  |  3  |  12

Is something like this even possible using SQL?

---

Create a table, "intervals", with 12 rows containing a single field, "iinterval", with values from 1 to 12.

iinterval
        1
        2
        3
        4
        5
        6
        7
        8
        9
       10
       11
       12

You can then do a cross join between the intervals table and those T1 rows where interval is zero. (I renamed the T1 fields Date, Hour, and Interval because those are all reserved words.)

SELECT T1.ID, T1.idate, T1.ihour, intervals.iinterval
FROM T1, intervals
WHERE T1.iinterval=0;

Since there is no JOIN condition specified, every T1 row (which has zero interval) will be joined with every intervals row.

ID  idate    ihour iinterval
 1  1/1/2011     1         1
 1  1/1/2011     1         2
 1  1/1/2011     1         3
 1  1/1/2011     1         4
 1  1/1/2011     1         5
 1  1/1/2011     1         6
 1  1/1/2011     1         7
 1  1/1/2011     1         8
 1  1/1/2011     1         9
 1  1/1/2011     1        10
 1  1/1/2011     1        11
 1  1/1/2011     1        12

If that gets what you wanted for the rows where interval equals zero, you can use it in a UNION query to add those rows to the T1 rows where the interval is not zero.

SELECT a.ID, a.idate, a.ihour, a.iinterval
FROM T1 AS a
WHERE a.iinterval<>0
UNION
SELECT T1.ID, T1.idate, T1.ihour, intervals.iinterval
FROM T1, intervals
WHERE T1.iinterval=0;

I hope this will point you to something useful, but doubt this is exactly what you ultimately want. My reservation is because of this condition: "Notice that there is an overlap of the intervals and a 0-flagged interval". I don't know what should happen in response to those overlaps.