-2

So my last question was marked as duplicate even though the linked answer (MySQL - Rows to Columns) had nothing to do with what I was asking. That post refers to pivot tables which probably won't work here. Posting again.

I have a mysql table that has the following data.

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

very little thought was put in to the schema for this data, so every second row shares the same data with a different identifier and sensor_level value. I want to make a query that will merge rows sharing a sensor_map_id, then add two additional columns holding the sensor_level data from the two rows called "level" and "temperature" respectively.

Once formatted, 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.

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

Community
  • 1
  • 1
user1901469
  • 1,169
  • 1
  • 10
  • 21
  • This is a basic pivot. It is no more complicated than that. – Strawberry Dec 19 '16 at 23:48
  • Can you give me an example of how you would do it then? – user1901469 Dec 19 '16 at 23:49
  • In your example data, the same `sensor_map_id` (e.g. 1380) has two `sensor_log_id` (788551 and 788552). In your desired pivoted table, there is only one column for `sensor_log_id` and your example output picked just one of the value (788551). Is that really what you want? – leeyuiwah Dec 19 '16 at 23:56
  • Yes that is fine. sensor_log_id does not have to be in the results – user1901469 Dec 20 '16 at 00:00

1 Answers1

0

Assuming your table is called data, the following query gets you the three key columns sensor_map_id, level, and tempature. You can add the other column as needed. For example, if either one of the sensor_log_id will do, you can just add tL.sensor_log_id or min(tL.sensor_log_id, tT.sensor_log_id)

select
    tL.sensor_map_id 
    , tL.level
    , tT.tempature
from 
    (
        select
            tL.sensor_map_id
            , tL.level
        from data tL
        where identifier='Level'
    ) as tL
    , (
        select
            tT.sensor_map_id
            , tT.tempature 
        from data tT
        where identifier='Tempature'
    ) as tT 
where 
    tL.sensor_map_id = tT.sensor_map_id
leeyuiwah
  • 6,562
  • 8
  • 41
  • 71