Oracle 11g Release 2

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

6 rows affected

SELECT SENSOR_TYPE,
       SENSOR,
       TO_NUMBER( moisture_value ) AS moisture_value,
       moisture_unit,
       TO_TIMESTAMP( moisture_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF3' ) AS moisture_timestamp,
       TO_NUMBER( conductivity_value ) AS conductivity_value,
       conductivity_unit,
       TO_TIMESTAMP( conductivity_timestamp, 'YYYY-MM-DD"T"HH24:MI:SS.FF3' ) AS conductivity_timestamp
FROM   (
  SELECT REGEXP_SUBSTR( topic, '[^/]+', 1, 1 ) AS sensor_type,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 2 ) AS sensor,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 4 ) AS measurement_name,
         REGEXP_SUBSTR( topic, '[^/]+', 1, 5 ) AS measurement_metadata_type,
         value
  FROM   table_name
)
PIVOT(
  MAX( value )
  FOR ( measurement_name, measurement_metadata_type )
  IN  (
    ( 'MOISTURE', 'value'     ) AS MOISTURE_value,
    ( 'MOISTURE', 'unit'      ) AS MOISTURE_unit,
    ( 'MOISTURE', 'timestamp' ) AS MOISTURE_timestamp,
    ( 'CONDUCTIVITY', 'value'     ) AS CONDUCTIVITY_value,
    ( 'CONDUCTIVITY', 'unit'      ) AS CONDUCTIVITY_unit,
    ( 'CONDUCTIVITY', 'timestamp' ) AS CONDUCTIVITY_timestamp
  )
)

SENSOR_TYPE	SENSOR	MOISTURE_VALUE	MOISTURE_UNIT	MOISTURE_TIMESTAMP	CONDUCTIVITY_VALUE	CONDUCTIVITY_UNIT	CONDUCTIVITY_TIMESTAMP
SMARTBASE	N0184	.414	Raw VWC	01-JAN-19 12.00.00.000000000 AM	.529	mS/cm	01-JAN-19 12.00.00.000000000 AM