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.
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,
SELECT device.serial_number AS 'device',
location.name AS 'location',
alerting.start AS 'start',
alerting.end AS 'end'
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;
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;