Oracle 18c

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

CREATE TABLE mytable(
   OH_IDNR   INTEGER  NOT NULL
  ,OCV_VNAME VARCHAR(37) NOT NULL
  ,OCV_VALUE VARCHAR(20) NOT NULL
);

begin
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_0_code_0','$[*].id');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_0_queryType','JSONPath');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_0_resourceName','profileIds#');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_0_saveTo','variable');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_0_useArray','TRUE');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_1_code_0','$[*].name');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_1_queryType','JSONPath');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_1_resourceName','profileNames#');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_1_saveTo','variable');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_1_useArray','TRUE');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_2_code_0','$[*].environment');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_2_queryType','JSONPath');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_2_resourceName','profileEnvironments#');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_2_saveTo','variable');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_2_useArray','TRUE');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_3_code_0','$[*].description');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_3_queryType','JSONPath');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_3_resourceName','profileDescriptions#');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_3_saveTo','variable');
INSERT INTO mytable(OH_IDNR,OCV_VNAME,OCV_VALUE) VALUES (8420518,'response_part_0_script_3_useArray','TRUE');
end;
/
1 rows affected

select
    oh_idnr,
    regexp_substr(ocv_vname, 'response_part_0_script_\d_') ocv_vname_common,
    max(case when ocv_vname like '%_code_0' then ocv_value end) code_0,
    max(case when ocv_vname like '%_queryType' then ocv_value end) queryType,
    max(case when ocv_vname like '%_resourceName' then ocv_value end) resourceName,
    max(case when ocv_vname like '%_saveTo' then ocv_value end) saveTo,
    max(case when ocv_vname like '%_useArray' then ocv_value end) useArray
from mytable 
group by oh_idnr, regexp_substr(ocv_vname, 'response_part_0_script_\d_')


OH_IDNR
OCV_VNAME_COMMON
CODE_0
QUERYTYPE
RESOURCENAME
SAVETO
USEARRAY


8420518
response_part_0_script_0_
$[*].id
JSONPath
profileIds#
variable
TRUE

8420518
response_part_0_script_1_
$[*].name
JSONPath
profileNames#
variable
TRUE

8420518
response_part_0_script_2_
$[*].environment
JSONPath
profileEnvironments#
variable
TRUE

8420518
response_part_0_script_3_
$[*].description
JSONPath
profileDescriptions#
variable
TRUE

OH_IDNR	OCV_VNAME_COMMON	CODE_0	QUERYTYPE	RESOURCENAME	SAVETO	USEARRAY
8420518	response_part_0_script_0_	$[*].id	JSONPath	profileIds#	variable	TRUE
8420518	response_part_0_script_1_	$[*].name	JSONPath	profileNames#	variable	TRUE
8420518	response_part_0_script_2_	$[*].environment	JSONPath	profileEnvironments#	variable	TRUE
8420518	response_part_0_script_3_	$[*].description	JSONPath	profileDescriptions#	variable	TRUE