Pivot row values matching a pattern into column names

I have data that look like this:

OH_IDNR  OCV_VNAME                              OCV_VALUE
8420518  response_part_0_script_0_code_0        $[*].id
8420518  response_part_0_script_0_queryType     JSONPath
8420518  response_part_0_script_0_resourceName  profileIds#
8420518  response_part_0_script_0_saveTo        variable
8420518  response_part_0_script_0_useArray      TRUE
8420518  response_part_0_script_1_code_0        $[*].name
8420518  response_part_0_script_1_queryType     JSONPath
8420518  response_part_0_script_1_resourceName  profileNames#
8420518  response_part_0_script_1_saveTo        variable
8420518  response_part_0_script_1_useArray      TRUE
8420518  response_part_0_script_2_code_0        $[*].environment
8420518  response_part_0_script_2_queryType     JSONPath
8420518  response_part_0_script_2_resourceName  profileEnvironments#
8420518  response_part_0_script_2_saveTo        variable
8420518  response_part_0_script_2_useArray      TRUE
8420518  response_part_0_script_3_code_0        $[*].description
8420518  response_part_0_script_3_queryType     JSONPath
8420518  response_part_0_script_3_resourceName  profileDescriptions#
8420518  response_part_0_script_3_saveTo        variable
8420518  response_part_0_script_3_useArray      TRUE

I would like to pivot these data so that for each set of rows with the same OH_IDNR and with the same common/non-unique part of OCV_VNAME, these columns are returned:

1. OH_IDNR
2. The common part of OCV_VNAME (e.g., ‘response_part_0_script_0_’)
3. – n. One column for each unique part of OCV_Name (e.g.,’ resourceName’), with OCV_VALUE as the value.

E.g.,

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

This obviously assumes that the unique part of OCV_VNAME is a string suitable for use as a column name. I’ve tried a few things, but the elegant solution eludes me.

Answer

You can do conditional aggregation:

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_')

Demo on DB Fiddle:

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    

---

You might find it more readable to parse in a subquery first:

select
    oh_idnr,
    ocv_vname_common,
    max(case when ocv_var_name = 'code_0' then ocv_value end) code_0,
    max(case when ocv_var_name = 'queryType' then ocv_value end) queryType,
    max(case when ocv_var_name = 'resourceName' then ocv_value end) resourceName,
    max(case when ocv_var_name = 'saveTo' then ocv_value end) saveTo,
    max(case when ocv_var_name = 'useArray' then ocv_value end) useArray
from (
    select 
        oh_idnr,
        regexp_substr(ocv_vname, 'response_part_0_script_d_') ocv_vname_common,
        regexp_replace(ocv_vname, 'response_part_0_script_d_', '') ocv_var_name,
        ocv_value 
    from mytable
) t
group by oh_idnr, ocv_vname_common

Demo on DB Fiddle