Can't create a MySQL query that generates 4 rows for each row in the table it references.

Question

I need to create a MySQL query that generates 4 rows for each row in the table it references. I need some of the information in those rows to repeat and some to be different. In the table each row stands for one day. I need to break the day up in 6 hour increments, hence the four rows for each entry. I need to create one column which for each day will have '12AM', '6AM', '12PM', and '6PM' values and another column will have the corresponding numeric values calculated for those entries.

Thanks a lot in advance and I will really appreciate any help on this.

Answer 1

You could do an insert with a SELECT statement, and use UNION to get the 4 records. Some SQL pseudocode is below.

INSERT INTO table1
          ( clock_time
          , some_value
          )
     SELECT '12AM'
          , sum(some_value)
       FROM ref_table
      WHERE ...
  UNION ALL
     SELECT '6AM'
          , sum(some_value)
       FROM ref_table
      WHERE ...
  UNION ALL
     SELECT '12PM'
          , sum(some_value)
       FROM ref_table
      WHERE ...
  UNION ALL
     SELECT '6PM'
          , sum(some_value)
       FROM ref_table
      WHERE ...

Answer 2

What the query should do and calculate with the values is a bit vague, but simply generating four rows for each row in another table is straight foward:

Select ...
From Table
    Cross Join  (
                Select '12 AM'
                Union All Select '6 AM'
                Union All Select '12 PM'
                Union All Select '6 PM'
                ) As Times

Answer 3

Create a table that contain your four time-points, and join with it to get the cartesian product.

select *
from days, hour_increments

The cartesian product of two tables is the join of every row in the first table to every row in the second. For instance, if we have the tables {1, 2, 3} and {a, b, c} then the product is {1a, 1b, 1c, 2a, 2b, 2c, 3a, 3b, 3c}.

How to make sensible calculations based on that dataset is another story that depends on the data and what you are trying to do.

Answer 4

Thank you so much for your suggestions. After cross joining I have a table where one column has '12AM', '6AM', '12PM' and '6PM' repeating for each day entry. Another column is supposed to have values calculated for those increments but is currently filled with NULLs. I need to fill that column with calculated values, which will come from some reference table. I don't know how to associate those calculated values with the correct time increments.

I will really appreciate any ideas. Thanks a lot in advance.