CODEWITHSUNDEEP
mysql
The Dude
The Dude

Reputation: 3

MYSQL splitting values into multiple columns based on column string

I have a table1 where I'm struggling trying to get an output like table2. Any ideas ?

Table1

+-----+-----------+------+----------+---------------------+
| id  | Sensor    | Temp | Humidity | DateTime            |
+-----+-----------+------+----------+---------------------+
|   1 | Sens1     |  9.7 |       55 | 2015-02-21 11:29:47 |
|   2 | Sens2     |  4.4 |       99 | 2015-02-21 11:45:00 |
|   3 | Sens1     |  9.8 |       56 | 2015-02-21 11:44:59 |
|   4 | Sens2     |  4.5 |       98 | 2015-02-21 11:59:24 |
+-----+-----------+------+----------+---------------------+

output wanted: Table2

+---------------------+-----------+----------+-----------+----------+
| Datetime            | Sens1temp | Sens1hum | Sens2temp | Sens2hum |
+---------------------+-----------+----------+-----------+----------+
| 2015-02-21 11:29:47 |      9.7  |       55 |    null   |    null  |
| 2015-02-21 11:45:00 |     null  |     null |     4.4   |      99  |
| 2015-02-21 11:44:59 |      9.8  |       56 |    null   |    null  |
| 2015-02-21 11:59:24 |     null  |     null |     4.5   |      98  |
+---------------------+-----------+----------+----------+-----------+

I've spent hours searching but just can't get it to work.

Upvotes: 0

Views: 27

Answers (1)

Raging Bull
Raging Bull

Reputation: 18767

Try this:

SELECT Datetime,
       CASE WHEN Sensor='Sens1' THEN Temp END AS Sens1temp,
       CASE WHEN Sensor='Sens1' THEN Humidity END AS Sens1hum,
       CASE WHEN Sensor='Sens2' THEN Temp END AS Sens2temp,
       CASE WHEN Sensor='Sens2' THEN Humidity END AS Sens2hum
FROM TableName
GROUP BY Datetime

Sample result in SQL Fiddle.

Upvotes: 1

Related Questions