Looking for a query solution

Question

I need help with a query solution

table: roomclimate

id	time	deviceId	temperature	humidity
1	2021-12-22 15:49:31	1	23.5	50
2	2021-12-22 15:49:31	2	10.5	50
3	2021-12-22 15:59:31	1	23.5	56
4	2021-12-22 15:59:31	2	10.7	57

and now i want to group the values like this

time	dev_1_temp	dev_1_humi	dev_2_temp	dev_2_humi
2021-12-22 15:49:31	23.5	50	10.5	50
2021-12-22 15:59:31	23.5	56	10.7	57

Welcome to Stack Overflow. Please take the [tour] to learn how Stack Overflow works and read [ask] on how to improve the quality of your question. Then see https://meta.stackoverflow.com/questions/271055/tips-for-asking-a-good-structured-query-language-sql-question/271056#271056 for SQL related questions. — Progman, Jan 01 '22 at 12:14
Does this answer your question? [How can I return pivot table output in MySQL?](https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql) — Stu, Jan 01 '22 at 12:16
thank for your answers! pivot is the soloution and i din't remember me at this.. — markus_he, Jan 01 '22 at 12:33

score 1 · Answer 1 · edited Jan 01 '22 at 16:36

1

So, this is working.. but is there an better Solution?

SELECT
    `time`,

    MAX(IF(`deviceId`=1,ROUND(`temperature`,1),NULL)) AS dev_1_temp,
    MAX(IF(`deviceId`=1,`humidity`,NULL)) AS dev_1_humi,
    MAX(IF(`deviceId`=2,ROUND(`temperature`,1),NULL)) AS dev_2_temp,
    MAX(IF(`deviceId`=2,`humidity`,NULL)) AS dev_2_humi
 

FROM tbl_klima
GROUP BY `time` DESC

edited Jan 01 '22 at 16:36

md2perpe

3,372
2
18
22

answered Jan 01 '22 at 16:16

markus_he

17
1

score 0 · Answer 2 · answered Jan 01 '22 at 16:41

0

Try this:

SELECT
    base.time,
    dev1.temperature, dev1.humidity,
    dev2.temperature, dev2.humidity
FROM roomclimate AS base
LEFT JOIN roomclimate AS dev1
    ON dev1.time = base.time AND dev1.deviceId = 1
LEFT JOIN roomclimate AS dev2
    ON dev2.time = base.time AND dev2.deviceId = 2
ORDER BY base.time DESC

I hope that you have an index on time.

answered Jan 01 '22 at 16:41

md2perpe

3,372
2
18
22

Thank you, this is working too.. but i have to change "ORDER BY" to "GROUP BY" – markus_he Jan 03 '22 at 08:54
@markus_he. I had to think some seconds why you need `GROUP BY`. It's because there are several rows in `base` with the same `time`. Did you notice any difference in speed between your solution and mine? – md2perpe Jan 03 '22 at 09:43

Looking for a query solution

2 Answers2