Jérôme B Jérôme B - 2 months ago 7
MySQL Question

Column as row concatenated with another column

So I have to use a database which has time as column (there's a column for every 10 min in a specific day), the

id
in here isn't a primary key even though it's unique (and a primary key) in another table. And the rows equals to a day.
So I'd like to have the value for each time slot with it's datetime on a row.

Is that even possible ?
I could write a little program that recreate a table with what I need but I'd like to have as less duplicate data as possible.

What I have :

+---+----------+-----+-----+-----+-----+
|ID | DATE |00h00|00h10|00h20|00h30|
+---+----------+-----+-----+-----+-----+
|1 |2016-09-28|80 |79 |75 |73 |
+---+----------+-----+-----+-----+-----+
|1 |2016-09-27|82 |80 |76 |74 |
+---+----------+-----+-----+-----+-----+


What I'd like to have :

+---+----------------+-----+
|ID | DATE |VALUE|
+---+----------------+-----+
|1 |2016-09-28 00h00|80 |
|1 |2016-09-28 00h10|79 |
|1 |2016-09-28 00h20|75 |
|1 |2016-09-28 00h30|73 |
+---+----------------+-----+
|1 |2016-09-28 00h00|82 |
|1 |2016-09-28 00h10|80 |
|1 |2016-09-28 00h20|76 |
|1 |2016-09-28 00h30|74 |
+---+----------------+-----+

Answer

The simplest way is to use union all:

select id, dateadd(date, interval 0 minute) as date, `00h00` as value from t union all
select id, dateadd(date, interval 10 minute) as date, `00h10` as value from t union all
. . .

If you have a numbers table, you can also do:

select t.id, dateadd(date, interval (n.n - 1) * 10 minute) as date,
       (case when n = 1 then `00h00`
             when n = 2 then `00h10`
             . . .
        end) as value
from t join
     numbers n
     on n.n < 24 * 6;

I don't think there is any way to do this in MySQL without listing all the columns in some way. You can use another query or spreadsheet to generate the SQL.