MariaDB 10.3

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE IF NOT EXISTS `measurement`
(
        `measureID`     int NOT NULL AUTO_INCREMENT ,
        `terraID`       int NOT NULL ,
        `pinID`         int NOT NULL ,
        `time`          timestamp NOT NULL ,
        `value`         float NULL ,
        `temperature`   boolean NOT NULL ,

        PRIMARY KEY (`measureID`),
        KEY `measure_terraID_FK` (`terraID`),
        KEY `measure_pinID_FK` (`pinID`)
) ENGINE=INNODB COLLATE=utf8mb4_unicode_ci;
CREATE TABLE mytable(
   measureID   INTEGER  NOT NULL PRIMARY KEY 
  ,terraID     INTEGER  NOT NULL
  ,pinID       INTEGER  NOT NULL
  ,time        VARCHAR(21) NOT NULL
  ,value       NUMERIC(8,3) NOT NULL
  ,temperature BIT  NOT NULL
);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (1,1,9,'2020-04-10 13:00:01',34.3,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (2,1,9,'2020-04-10 13:00:01',26.5,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (3,2,10,'2020-04-10 13:00:01',35.1,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (4,2,10,'2020-04-10 13:00:01',32.9,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (5,1,9,'2020-04-10 13:05:01',34.4,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (6,1,9,'2020-04-10 13:05:01',26.6,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (7,2,10,'2020-04-10 13:05:01',35,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (8,2,10,'2020-04-10 13:05:01',33,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38087,2,10,'2020-08-31 12:50:02',35.9,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38088,2,10,'2020-08-31 12:50:02',35,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38089,1,11,'2020-08-31 12:50:02',25.187,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38090,2,12,'2020-08-31 12:50:02',28.312,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38091,2,10,'2020-08-31 12:55:01',35.8,0);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38092,2,10,'2020-08-31 12:55:01',35,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38093,1,11,'2020-08-31 12:55:01',25.25,1);
INSERT INTO mytable(measureID,terraID,pinID,time,value,temperature) VALUES (38094,2,12,'2020-08-31 12:55:01',28.375,1);
select 
    max(measureID) measureID, 
    terraID, 
    pinID, 
    time, 
    max(case when temperature = 1 then value end) value,
    max(case when temperature = 0 then value end) humidity
from mytable
group by terraID, pinID, time
order by min(measureID)


measureID
terraID
pinID
time
value
humidity


2
1
9
2020-04-10 13:00:01
26.500
34.300

4
2
10
2020-04-10 13:00:01
32.900
35.100

6
1
9
2020-04-10 13:05:01
26.600
34.400

8
2
10
2020-04-10 13:05:01
33.000
35.000

38088
2
10
2020-08-31 12:50:02
35.000
35.900

38089
1
11
2020-08-31 12:50:02
25.187
null

38090
2
12
2020-08-31 12:50:02
28.312
null

38092
2
10
2020-08-31 12:55:01
35.000
35.800

38093
1
11
2020-08-31 12:55:01
25.250
null

38094
2
12
2020-08-31 12:55:01
28.375
null

measureID	terraID	pinID	time	value	humidity
2	1	9	2020-04-10 13:00:01	26.500	34.300
4	2	10	2020-04-10 13:00:01	32.900	35.100
6	1	9	2020-04-10 13:05:01	26.600	34.400
8	2	10	2020-04-10 13:05:01	33.000	35.000
38088	2	10	2020-08-31 12:50:02	35.000	35.900
38089	1	11	2020-08-31 12:50:02	25.187	null
38090	2	12	2020-08-31 12:50:02	28.312	null
38092	2	10	2020-08-31 12:55:01	35.000	35.800
38093	1	11	2020-08-31 12:55:01	25.250	null
38094	2	12	2020-08-31 12:55:01	28.375	null