By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
+--------------------------+------------+-----------------+---------+------+-------+-------------+-------------------------+----------------------------+-------+-------+
| deviceID | date | timestamp | counter | rssi | vavId | nvo_airflow | nvo_air_damper_position | nvo_temperature_sensor_pps | block | floor |
+--------------------------+------------+-----------------+---------+------+-------+-------------+-------------------------+----------------------------+-------+-------+
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:40:01.513066 | 805 | 91 | 7 | NULL | NULL | 26.49 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:41:01.542272 | 807 | 94 | 5 | 50 | 64 | 26.37 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:43:01.699023 | 811 | 90 | 7 | 50 | NULL | NULL | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:46:01.412259 | 817 | 64 | 26 | NULL | NULL | 25.85 | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:48:01.576133 | 821 | 91 | 26 | 55 | 42 | NULL | NULL | ABCD |
| fd00::212:4b00:1957:d616 | 2020-02-29 | 12:49:01.529593 | 823 | 91 | 7 | 45 | 72 | NULL | NULL | ABCD |
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '+--------------------------+------------+-----------------+---------+------+----' at line 1
create table t(
deviceID varchar(30)
,date1 date
,timestamp1 time
,vavId int
,nvo_airflow int
,nvo_air_damper_position int
,nvo_temperature_sensor_pps int
);
insert into t values('fd00::212:4b00:1957:d616','2020-02-29','12:40:01.513066',7,NULL,NULL,26.49)
insert into t values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:41:01.542272',5,50,64,26.37)
insert into t values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:43:01.699023',7,50,NULL,NULL)
insert into t values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:46:01.412259',26,NULL,NULL,25.85)
insert into t values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:48:01.576133',26,55,42,NULL)
insert into t values('fd00::212:4b00:1957:d616',' 2020-02-29 ','12:49:01.529593',7,45,72, NULL )
with data
as (
select *
,case when nvo_airflow is null then 0 else 1 end as airflow_flg
,case when nvo_air_damper_position is null then 0 else 1 end as damper_flg
,case when nvo_temperature_sensor_pps is null then 0 else 1 end as sensor_flg
,row_number() over(partition by case when nvo_airflow is null then 0 else 1 end,deviceid,vavid order by timestamp1 desc) as rnk_airflow
,row_number() over(partition by case when nvo_air_damper_position is null then 0 else 1 end,deviceid,vavid order by timestamp1 desc) as rnk_damper
,row_number() over(partition by case when nvo_temperature_sensor_pps is null then 0 else 1 end,deviceid,vavid order by timestamp1 desc) as rnk_sensor
from t
)
,concat_data
as (
select deviceid
,vavid
,nvo_airflow as val
,'nvo_airflow' as txt
from data
where airflow_flg=1
and rnk_airflow=1
union all
select deviceid
,vavid
,nvo_air_damper_position as val
,'nvo_air_damper_position' as txt
from data
where damper_flg=1
and rnk_damper=1
union all
select deviceid
,vavid
,nvo_temperature_sensor_pps as val
,'nvo_temperature_sensor_pps' as txt
from data
where sensor_flg=1
and rnk_sensor=1
| deviceid | vavid | nvo_airflow | nvo_air_damper_position | nvo_temperature_sensor_pps |
|---|---|---|---|---|
| fd00::212:4b00:1957:d616 | 7 | 45 | 72 | 26 |
| fd00::212:4b00:1957:d616 | 26 | 55 | 42 | 26 |
| fd00::212:4b00:1957:d616 | 5 | 50 | 64 | 26 |