Reorganizing MySQL table to multiple rows by timestamp.

Question

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

Answer 1

SELECT
    time,
    SUM(CASE WHEN probe_id = '00A' THEN position ELSE 0 END) `00A`,
    SUM(CASE WHEN probe_id = '00B' THEN position ELSE 0 END) `00B`,
    SUM(CASE WHEN probe_id = '00C' THEN position ELSE 0 END) `00C`,
    SUM(CASE WHEN probe_id = '01A' THEN position ELSE 0 END) `01A`,
    SUM(CASE WHEN probe_id = '01B' THEN position ELSE 0 END) `01B`,
    SUM(CASE WHEN probe_id = '01C' THEN position ELSE 0 END) `01C`
FROM MG41
GROUP BY time LIMIT 10;

Answer 2

To have a dynamic number of rows, you will need a dynamically generated query. (If you know in advance the probe_ids, then you can juse use a static query.)

The general form will be

    SELECT time, P00A, P00B, ... P03B, etc..
    FROM 
       (SELECT time FROM MG41 GROUP BY time) allTimes
    INNER JOIN 
        (SELECT time, position AS P00A from MG41 WHERE probe_id='00A') pt00A 
        ON pt00A.time=allTimes.time   
   INNER JOIN 
        (SELECT time, position AS P00B from MG41 WHERE probe_id='00B') pt00B 
        ON pt00B.time=allTimes.time

etc...

You then build out the INNER JOINs and the SELECTed rows for all the different probes.