0

I have a mysql query that is giving the following results.

+-------------+---------------+---------------+--------------------+-    --------------------+------------+--------------+
| identifier  | sensor_map_id | sensor_log_id | device_post_log_id | sensor_component_id | logged_at  | sensor_level |
+-------------+---------------+---------------+--------------------+-    --------------------+------------+--------------+
| Level       |          1380 |        788551 |             107392 |                2759 | 1463690100 |        38.00 |
| Temperature |          1380 |        788552 |             107392 |                2760 | 1463690100 |       300.60 |
| Level       |          1381 |        788547 |             107392 |                2761 | 1463690100 |        46.00 |
| Temperature |          1381 |        788548 |             107392 |                2762 | 1463690100 |       299.50 |
| Level       |          1382 |        788549 |             107392 |                2763 | 1463690100 |        45.00 |
| Temperature |          1382 |        788550 |             107392 |                2764 | 1463690100 |       299.50 |
| Level       |          1383 |        100381 |              12664 |                2765 | 1454111400 |       153.00 |
| Temperature |          1383 |        100382 |              12664 |                2766 | 1454111400 |       295.20 |
| Level       |          1383 |        788553 |             107392 |                2773 | 1463690100 |        38.00 |
| Temperature |          1383 |        788554 |             107392 |                2774 | 1463690100 |       300.40 |
+-------------+---------------+---------------+--------------------+---------------------+------------+--------------+

The data is essentially the duplicated twice for each sensor_map_id. Once for a "Level" and once for a Temperature. The column sensor_level refers to either the "level" or the "Temperature" based on the identifier column. Obviously the long term solution is to change the way this data is stored, but for now I would like to take these results and essentially merge the rows that share a sensor_map_id, then instead of a sensor_level column, have two new columns, level and temperature to store the values currently held in sensor_level while making more sense.

The data should instead look something like this with half as many rows but each row has more columns. 

+-------------+---------------+---------------+--------------------+-    --------------------+------------+---------+
| sensor_map_id | sensor_log_id | device_post_log_id | sensor_component_id | logged_at  | level        | temperature
+---------------+---------------+--------------------+-    --------------------+------------+--------------+--------
| 1380          |        788551 |             107392 |                2759 | 1463690100 |        38.00 | 300.60
| 1381          |        788547 |             107392 |                2761 | 1463690100 |        46.00 | 299.50
| 1382          |        788549 |             107392 |                2763 | 1463690100 |        45.00 | 299.50
| 1383          |        100381 |              12664 |                2765 | 1454111400 |       153.00 | 295.20
| 1383          |        788553 |             107392 |                2773 | 1463690100 |        38.00 | 300.40
+-------------+---------------+---------------+--------------------+---------------------+------------+-------------+

I'd imagine this is easy to do but Mysql is not my forte. Thanks in advance for the help!

Edit: I am not sure why this is marked as duplicate. The other question is asking about pivot tables, this is more complicated.

user1901469
  • 1,169
  • 1
  • 10
  • 21
  • So basically you want to get only the rows where you have `Level` in the identifier? – Dekel Dec 19 '16 at 23:04
  • What does your query look like? – Jhorra Dec 19 '16 at 23:08
  • No sorry I will re-iterate. If you look at the data, every 1st and second rows are the same except for "Identifier" and sensor_level. This is because the data was stored in two separate rows instead of storing it all in one row with additional columns. I want to take the rows where sensor_map_id A = sensor_map_id B and in these rows take sensor_level A and store it as "level" and sensor_level B gets stored as "temperature" The result will look like the second table. – user1901469 Dec 19 '16 at 23:09
  • @jhorra the query is irrelevant I am looking to transform the result without touching that (one change at a time) – user1901469 Dec 19 '16 at 23:10
  • @shmosel this is not a duplicate.... the linked question is similar but not asking the same thing at all – user1901469 Dec 19 '16 at 23:44

0 Answers0