Oracle 21c

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

CREATE TABLE weather (READING_DATE, HOUR_VALUE, STATION_ID, TEMPERATURE, HUMIDITY, WIND, CONDITIONS) AS
SELECT DATE '2022-01-08', 1, 'station1', 77,  50, 1, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 'station2', 75,  50, 0, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 1, 'station3', 74,  60, 3, 'overcast' FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station1', 76,  50, 3, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station2', 74,  50, 0, 'clear'    FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 'station3', 70,  65, 2, 'overcast' FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station1', 73,  60, 5, 'cloudy'   FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station2', 71,  70, 2, 'cloudy'   FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 'station3', 69, 100, 3, 'rain'     FROM DUAL;
9 rows affected

CREATE TABLE telemetry (READING_DATE, HOUR_VALUE, TELEMETRY_VALUE_1, TELEMETRY_VALUE_2) AS
SELECT DATE '2022-01-08', 1, 430, 10 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 2, 405,  9 FROM DUAL UNION ALL
SELECT DATE '2022-01-08', 3, 390,  8 FROM DUAL;
3 rows affected

SELECT t.*,
       w.c1_temp,
       w.c1_hum,
       w.c1_wind,
       w.c1_cond,
       w.c2_temp,
       w.c2_hum,
       w.c2_wind,
       w.c2_cond,
       w.c3_temp,
       w.c3_hum,
       w.c3_wind,
       w.c3_cond
FROM   telemetry t
       INNER JOIN (
         SELECT *
         FROM   weather
         PIVOT (
           AVG(temperature) AS temp,
           AVG(humidity) AS hum,
           AVG(wind) AS wind,
           MAX(conditions) KEEP (DENSE_RANK FIRST ORDER BY ROWNUM) AS cond
           FOR station_id IN (
             'station1' AS c1,
             'station2' AS c2,
             'station3' AS c3
           )
         )
       ) w
       ON (   t.reading_date = w.reading_date
          AND t.hour_value   = w.hour_value)


READING_DATE
HOUR_VALUE
TELEMETRY_VALUE_1
TELEMETRY_VALUE_2
C1_TEMP
C1_HUM
C1_WIND
C1_COND
C2_TEMP
C2_HUM
C2_WIND
C2_COND
C3_TEMP
C3_HUM
C3_WIND
C3_COND


08-JAN-22
1
430
10
77
50
1
clear
75
50
0
clear
74
60
3
overcast

08-JAN-22
2
405
9
76
50
3
clear
74
50
0
clear
70
65
2
overcast

08-JAN-22
3
390
8
73
60
5
cloudy
71
70
2
cloudy
69
100
3
rain

READING_DATE	HOUR_VALUE	TELEMETRY_VALUE_1	TELEMETRY_VALUE_2	C1_TEMP	C1_HUM	C1_WIND	C1_COND	C2_TEMP	C2_HUM	C2_WIND	C2_COND	C3_TEMP	C3_HUM	C3_WIND	C3_COND
08-JAN-22	1	430	10	77	50	1	clear	75	50	0	clear	74	60	3	overcast
08-JAN-22	2	405	9	76	50	3	clear	74	50	0	clear	70	65	2	overcast
08-JAN-22	3	390	8	73	60	5	cloudy	71	70	2	cloudy	69	100	3	rain