separating values from single table then merge back

Question

I'm tying to query if possible to separate the values into different columns using the same table

so here is my sample table:

select date, tool, value from table

+----------------------+-----------+-------+
|        date          |  tool     | value |  
|                      |           |       |
| 2017-03-11 13:03:38  |  machineA |   14  |
| 2017-03-11 13:03:39  |  machineA |   18  |
| 2017-03-11 13:03:40  |  machineB |   50  |
| 2017-03-11 13:03:41  |  machineB |   50  |
+----------------------+-----------+-------+

And trying to have output like this: On my code I am looping this query then put in the dataset then re-organize the values, But now I'm not sure which function is reliable on this query should I need to do the joins or union or have some conditions.

+----------------------+-----------+----------+
|        date          |  machineA | machineB |  
|                      |           |          |
| 2017-03-11 13:03:38  |    14     |     0    |
| 2017-03-11 13:03:39  |    18     |     0    |
| 2017-03-11 13:03:40  |     0     |    50    |
| 2017-03-11 13:03:41  |     0     |    50    |
+----------------------+-----------+----------+

any suggestion and recommendation thanks in advance.

actually the column tool has 4 unique values only and that is machineA until machineD. — Syntax Rommel, Mar 12 '18 at 03:17
Then you have the answer here- https://stackoverflow.com/questions/3392956/sql-how-to-transpose — Udayraj Deshmukh, Mar 12 '18 at 03:18
Possible duplicate of [SQL - How to transpose?](https://stackoverflow.com/questions/3392956/sql-how-to-transpose) — Udayraj Deshmukh, Mar 12 '18 at 03:18

Frank AK · Accepted Answer · 2018-03-12T03:26:20.510

try the below query, I have create a table as t02, you may have another name, just change it as yours.

mysql> select * from t02;
+---------------------+----------+-------+
| date                | tool     | value |
+---------------------+----------+-------+
| 2017-03-11 13:03:39 | machineB |    50 |
| 2017-03-11 13:03:41 | machineB |    50 |
| 2017-03-11 13:03:41 | machineA |    14 |
| 2017-03-11 13:03:42 | machineA |    18 |
+---------------------+----------+-------+

Let me show you how to do it step by step, first let try to pick up one!

select date, (tool='machineA') as machineA, (tool='machineB') as machineB from t02;

+---------------------+----------+----------+
| date                | machineA | machineB |
+---------------------+----------+----------+
| 2017-03-11 13:03:39 |        0 |        1 |
| 2017-03-11 13:03:41 |        0 |        1 |
| 2017-03-11 13:03:41 |        1 |        0 |
| 2017-03-11 13:03:42 |        1 |        0 |
+---------------------+----------+----------+

select date, if((tool='machineA'), value, 0) as machineA, if((tool='machineB'), value, 0) as machineB from t02;

and then you should got the below result:

+---------------------+----------+----------+
| date                | machineA | machineB |
+---------------------+----------+----------+
| 2017-03-11 13:03:39 |        0 |       50 |
| 2017-03-11 13:03:41 |        0 |       50 |
| 2017-03-11 13:03:41 |       14 |        0 |
| 2017-03-11 13:03:42 |       18 |        0 |
+---------------------+----------+----------+

If you have machineC and machineD , etc. you just do it with the same way will be fine if((tool='machineX'), value, 0) as machineX

Cool this is more shorter compare to my existing query and much readable, perfect! — Syntax Rommel, Mar 12 '18 at 03:28

separating values from single table then merge back

1 Answers1