How to transpose MySQL tables unique row into column as a query

Question

The Data:

I have the following MySQL table:

Timestamp           | MachineID |  Arrival_Timestamp  | CobID | Data |
--------------------+-----------+---------------------+-------+------| 
2017-07-22 20:03:07 |   1217    | 2017-08-08 04:46:02 | C237  | 10   | 
2017-07-22 20:03:08 |   1217    | 2017-08-08 04:47:03 | C237  | 11   | 
2017-07-22 20:03:09 |   1217    | 2017-08-08 04:48:04 | C237  | 12   | 
2017-07-22 20:07:17 |   1218    | 2017-08-08 04:49:05 | C234  | 20   | 
2017-07-22 20:05:19 |   1224    | 2017-08-08 04:50:06 | C239  | 30   | 
2017-07-22 20:06:17 |   1216    | 2017-08-08 04:50:07 | C237  | 40   | 
2017-07-22 20:08:07 |   1224    | 2017-08-08 04:50:08 | C234  | 50   | 
2017-07-22 20:09:14 |   1216    | 2017-08-08 04:55:09 | C239  | 60   |
2017-07-22 20:09:14 |   1216    | 2017-08-08 04:55:10 | C239  | 61   | 
2017-07-22 20:09:17 |   1216    | 2017-08-08 04:55:12 | C239  | 63   | 
2017-07-22 20:09:18 |   1216    | 2017-08-08 04:56:02 | C239  | 63   | 
2017-07-22 20:09:19 |   1216    | 2017-08-08 04:56:03 | C239  | 63   | 
2017-07-22 20:09:34 |   1217    | 2017-08-08 04:56:21 | C234  | 70   | 
2017-07-22 20:09:56 |   1224    | 2017-08-08 04:57:23 | C237  | 80   | 
2017-07-22 20:09:58 |   1299    | 2017-08-08 04:57:43 | C239  | 90   | 
2017-07-22 20:09:59 |   1218    | 2017-08-08 04:57:55 | C239  | 100  | 
2017-07-22 20:10:04 |   1218    | 2017-08-08 04:58:03 | C234  | 110  | 
2017-07-22 20:10:17 |   1217    | 2017-08-08 04:58:04 | C239  | 120  |

From this table I would like to count all the entries/rows for all the unique CobID of all unique MachineIDs.

The Goal:

I want to write an Query where the output will be:

     | 1216 | 1217 | 1218 | 1224 | 1299 |
-----+------+------+------+------+------|
C234 | 0    | 1    | 1    | 2    | 0    |
C237 | 1    | 3    | 0    | 1    | 0    |
C239 | 5    | 1    | 1    | 1    | 1    |

So this Query, I could look at MachineID 1216 and say there are 0 entries for C234 while there is 5 entries for C239.

What I have done so far:

I have tried the following query, which gets me what I want, but not in the exact format I want:

data_query = "SELECT `MachineID`, `CobID`, COUNT(*) FROM `my_table` WHERE `Arrival_Timestamp`  between \'{}\' AND \'{}\'  GROUP BY `CobID`, `MachineID` ORDER BY `CobID` ASC".format(
        start_date, end_date)

Question:

How can I change the query to get it into the aforementioned format?

score 0 · Answer 1 · answered Aug 08 '17 at 06:30

Try using a CASE where you only SUM 1 if it 's the correc column, if you have more machine types, you can add more columns:

data_query="SELECT CobID,
       SUM(case 
        when MachineID='1216' then 1  
        else 0 
        end) as '1216', 
       SUM(case 
        when MachineID='1217' then 1  
        else 0 
        end) as '1217',
       SUM(case 
        when MachineID='1218' then 1  
        else 0 
        end) as '1218', 
       SUM(case 
        when MachineID='1224' then 1  
        else 0 
        end) as '1224', 
       SUM(case 
        when MachineID='1299' then 1  
        else 0 
        end) as '1299'
FROM my_table
WHERE `Arrival_Timestamp`  between \'{}\' AND \'{}\'
group by CobID
ORDER BY `CobID` ASC".format(start_date, end_date)

How to transpose MySQL tables unique row into column as a query

1 Answers1