SQL Filtering Rows by column value, and creating new column by filtered value

user_18976285 0
Viewed 45

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 |
plc mysql sql
1 Answers

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

user_189762850
Related