CODEWITHSUNDEEP
sqloracle-databasegroup-bypivot
mlowry
mlowry

Reputation: 171

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.

Upvotes: 0

Views: 349

Answers (2)

GMB
GMB

Reputation: 222652

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270823

To me, this looks like an aggregation:

select OH_IDNR,
       substr(OCV_VNAME, 1, 25) as OCV_VNAME_common,
       max(case when OCV_VNAME_common like '%code_0' then OCV_VALUE end) as code_0,
       max(case when OCV_VNAME_common like '%queryType' then OCV_VALUE end) as queryType,
       max(case when OCV_VNAME_common like '%resourceName' then OCV_VALUE end) as resourceName,
       max(case when OCV_VNAME_common like '%saveTo' then OCV_VALUE end) as saveTo,
       max(case when OCV_VNAME_common like '%useArray' then OCV_VALUE end) as useArray
from t
group by OH_IDNR, substr(OCV_VNAME, 1, 25);

I would emphasize the this is based on your sample data. It is unclear if there is a better method to assign the prefix. One idea is to use regular expressions:

select OH_IDNR,
       regexp_replace(OCV_VNAME, '^(.*)(code_0|queryType|resourceName|saveTo|useArray)$', '\1'),
       max(case when OCV_VNAME_common like '%code_0' then OCV_VALUE end) as code_0,
       max(case when OCV_VNAME_common like '%queryType' then OCV_VALUE end) as queryType,
       max(case when OCV_VNAME_common like '%resourceName' then OCV_VALUE end) as resourceName,
       max(case when OCV_VNAME_common like '%saveTo' then OCV_VALUE end) as saveTo,
       max(case when OCV_VNAME_common like '%useArray' then OCV_VALUE end) as useArray
from t
group by OH_IDNR,
         regexp_replace(OCV_VNAME, '^(.*)(code_0|queryType|resourceName|saveTo|useArray)$', '\1');

Upvotes: 1

Related Questions