user3285817 user3285817 - 5 months ago 16
MySQL Question

MySQL - Return Undetermined Number of Columns in a Single Row, related to a common key

I have many devices connected by a mesh network. Each device has a serial number. Each device sends out an hourly message to say "I'm still here." The server records periods during which it did not receive that message, a state we call "stale".

Each instance of a device being stale creates a new row in the "alerting" table of our database.

What I want to do is create a report that has only a single row for each device that has ever been stale, with the starting and ending timestamps of each instance of that device being stale being columns in the row, proceeding to the right until there are no more instances of staleness.

Example output:

SERIAL_NUMBER, START, END, START, END, START, END . . .
18462G, 2016-02-03 12:45, 2016-02-03-18:43, 2016-02-05 09:55, 2016-02-07 19:11
T8837G, 2016-02-05 02:34,
7465LT, 2016-02-02 17:45, 2016-02-05 18:13,


In this example device 18462G has been stale 3 times (meaning it has 3 rows in the "alerting" table), but is currently not stale.

T8837G has been stale once (1 row in the alerting table), and is still stale (no end date).

7465LT has been stale twice (2 rows in alerting table), but is currently active.

I tried a "GROUP BY" in my MySQL but it doesn't seem to be working.

SELECT device.serial_number AS 'device',
location.name AS 'location',
alerting.start AS 'start',
alerting.end AS 'end'
FROM alerting
JOIN device ON alerting.device_id = device.id
JOIN device_location ON device_location.device_id = device.id
JOIN location ON device_location.location_id = location.id
GROUP BY device
ORDER BY location, device;

Answer

A possible way (similar) but not exactly like in your sample is based on group_concat and concat

    SELECT device.serial_number AS device,
    location.name AS location,
    group_concat(concat('start : ',alerting.start,'end : ',alerting.end))
    FROM alerting
    JOIN device ON alerting.device_id = device.id
    JOIN device_location ON device_location.device_id = device.id    
    JOIN location ON device_location.location_id = location.id
    GROUP BY device.serial_number, location.name
    ORDER BY location.name, device.serial_number;