Reputation: 3
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
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