CODEWITHSUNDEEP
mysqlsqlunpivot
Jérôme B
Jérôme B

Reputation: 310

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   |
+---+----------------+-----+

Upvotes: 3

Views: 57

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270463

The simplest way is to use union all:

select id, date_add(date, interval 0 minute) as date, `00h00` as value from t union all
select id, date_add(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, date_add(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.

Upvotes: 4

Related Questions