MYSQL splitting values into multiple columns based on column string

Question

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.

Raging Bull · Accepted Answer · 2015-02-23 19:35:44Z

1

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.

answered Feb 23, 2015 at 19:35

Raging Bull

18.8k13 gold badges52 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alex Over a year ago

we don't need MAX here :) CASE is enough imho

Collectives™ on Stack Overflow

MYSQL splitting values into multiple columns based on column string

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related