Reorganizing MySQL table to multiple rows by timestamp.
- by Ben Burleson
OK MySQL Wizards:
I have a table of position data from multiple probes defined as follows:
+----------+----------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+----------+------+-----+---------+-------+
| time | datetime | NO | | NULL | |
| probe_id | char(3) | NO | | NULL | |
| position | float | NO | | NULL | |
+----------+----------+------+-----+---------+-------+
A simple select outputs something like this:
+---------------------+----------+----------+
| time | probe_id | position |
+---------------------+----------+----------+
| 2010-05-05 14:16:42 | 00A | 0.0045 |
| 2010-05-05 14:16:42 | 00B | 0.0005 |
| 2010-05-05 14:16:42 | 00C | 0.002 |
| 2010-05-05 14:16:42 | 01A | 0 |
| 2010-05-05 14:16:42 | 01B | 0.001 |
| 2010-05-05 14:16:42 | 01C | 0.0025 |
| 2010-05-05 14:16:43 | 00A | 0.0045 |
| 2010-05-05 14:16:43 | 00B | 0.0005 |
| 2010-05-05 14:16:43 | 00C | 0.002 |
| 2010-05-05 14:16:43 | 01A | 0 |
| . | . | . |
| . | . | . |
| . | . | . |
+---------------------+----------+----------+
However, I'd like to output something like this:
+---------------------+--------+--------+-------+-----+-------+--------+
| time | 00A | 00B | 00C | 01A | 01B | 01C |
+---------------------+--------+--------+-------+-----+-------+--------+
| 2010-05-05 14:16:42 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| 2010-05-05 14:16:43 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| 2010-05-05 14:16:44 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| 2010-05-05 14:16:45 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| 2010-05-05 14:16:46 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| 2010-05-05 14:16:47 | 0.0045 | 0.0005 | 0.002 | 0 | 0.001 | 0.0025 |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
| . | . | . | . | . | . | . |
+---------------------+--------+--------+-------+-----+-------+--------+
Ideally, the different probe position columns are dynamically generated based on data in the table. Is this possible, or am I pulling my hair out for nothing?
I've tried GROUP BY time with GROUP_CONCAT that roughly gets the data out, but I can't separate that output into probe_id columns.
mysql SELECT time, GROUP_CONCAT(probe_id), GROUP_CONCAT(position) FROM MG41 GROUP BY time LIMIT 10;
+---------------------+-------------------------+------------------------------------+
| time | GROUP_CONCAT(probe_id) | GROUP_CONCAT(position) |
+---------------------+-------------------------+------------------------------------+
| 2010-05-05 14:16:42 | 00A,00B,00C,01A,01B,01C | 0.0045,0.0005,0.002,0,0.001,0.0025 |
| 2010-05-05 14:16:43 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:44 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:45 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:46 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:47 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:48 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:49 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:50 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
| 2010-05-05 14:16:51 | 01C,01B,01A,00C,00B,00A | 0.0025,0.001,0,0.002,0.0005,0.0045 |
+---------------------+-------------------------+------------------------------------+