Jérôme B Jérôme B - 1 year ago 65
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 Source

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.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download