'SQL Filtering Rows by column value, and creating new column by filtered value
I have a problem with one task in a DB
I'm loading data from plc to DB (mySql), and they are read as
id,timestamp,name,value.
I have to make it in form:
timestamp,value1, value2, value 3 etc
filtering for value need to be done by name value
My actual select result
| ID | Timestamp | Name | Value |
|---|---|---|---|
| 1 | 2022-02-16 16:38:49 | PT_TS1 | 21.5 |
| 2 | 2022-02-16 16:38:49 | PT_TS2 | 23.2 |
| 3 | 2022-02-16 16:38:49 | PT_TS3 | 21.0 |
| 4 | 2022-02-16 16:38:49 | PT_TS4 | 22.2 |
| 5 | 2022-02-16 16:38:50 | PT_TS5 | 21.0 |
| 6 | 2022-02-16 16:38:50 | PT_TS6 | 23.2 |
| 7 | 2022-02-16 16:38:50 | PT_TS7 | 20.3 |
| 8 | 2022-02-16 16:38:50 | PT_TS8 | 22.2 |
| 9 | 2022-02-16 16:38:50 | PT_TS_med | 21.825 |
| 10 | 2022-02-16 16:38:50 | Ccl_RH1 | 37.8514 |
| 11 | 2022-02-16 16:38:50 | Ccl_RH2 | 37.7514 |
| 12 | 2022-02-16 16:38:50 | vAhu2_SetPoint | 27.0 |
| 13 | 2022-02-16 16:40:25 | PT_TS1 | 21.5 |
| 14 | 2022-02-16 16:40:25 | PT_TS2 | 23.2 |
| 15 | 2022-02-16 16:40:25 | PT_TS3 | 21.0 |
| 16 | 2022-02-16 16:40:25 | PT_TS4 | 22.2 |
| 17 | 2022-02-16 16:40:25 | PT_TS5 | 21.0 |
| 18 | 2022-02-16 16:40:25 | PT_TS6 | 23.2 |
| 19 | 2022-02-16 16:40:25 | PT_TS7 | 20.3 |
| 20 | 2022-02-16 16:40:25 | PT_TS8 | 22.2 |
| 21 | 2022-02-16 16:40:25 | PT_TS_med | 21.825 |
| 22 | 2022-02-16 16:40:25 | Ccl_RH1 | 37.8697 |
| 23 | 2022-02-16 16:40:25 | Ccl_RH2 | 37.7697 |
| 24 | 2022-02-16 16:40:25 | vAhu2_SetPoint | 27.0 |
I would want to have something like this:
|TimeStamp |PT_TS1 |PT_TS2 |PT_TS3 |PT_TS4 |PT_TS5 |PT_TS6 |PT_TS6 |PT_TS7 |PT_TS8 |
Solution 1:[1]
Okay so i manage to do something like that:
SELECT CONVERT('timestamp'(16), @date ,20) AS datetime
group_concat(if(lab_ccl_monitor.Name='PT_TS1',Value, NULL)) as T1,
group_concat(if(lab_ccl_monitor.Name='PT_TS2',Value, NULL)) as T2,
group_concat(if(lab_ccl_monitor.Name='PT_TS3',Value, NULL)) as T3,
group_concat(if(lab_ccl_monitor.Name='PT_TS4',Value, NULL)) as T4,
group_concat(if(lab_ccl_monitor.Name='PT_TS5',Value, NULL)) as T5,
group_concat(if(lab_ccl_monitor.Name='PT_TS6',Value, NULL)) as T6,
group_concat(if(lab_ccl_monitor.Name='PT_TS7',Value, NULL)) as T7,
group_concat(if(lab_ccl_monitor.Name='PT_TS8',Value, NULL)) as T8
from
lab_ccl_monitor
group by
Timestamp ;
and it works, but now i have problem, that i need join rows by nearest timestamp. Sometimes my timestamp is diffrent in one cycle. i mean, that one have 54s and second 55 s. is any way to do that
Sources
This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.
Source: Stack Overflow
| Solution | Source |
|---|---|
| Solution 1 | Seynarim |