将匹配模式的行值透视到列名中

Question

我有这样的数据：

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

我想透视这些数据，以便对于具有相同OH_IDNR和OCV_VNAME的相同公共/非唯一部分的每组行，返回以下列：

1. OH_IDNR
2. OCV_VNAME的公共部分(例如，"response_part_0_script_0_"(
3. –n.OCV_Name的每个唯一部分(例如"resourceName"(对应一列，值为OCV_VALUE

例如

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

这显然假设OCV_VNAME的唯一部分是一个适合用作列名的字符串。我尝试了一些方法，但我无法找到优雅的解决方案。

Answer 1

您可以进行条件聚合：

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

DB Fiddle上的演示：

OH_IDNR|OCV_VNAME_COMMON|CODE_0|QUERYTYPE|RESOURCENAME|SAVETO|USEARRAY------：|：-----------------------------------------|：--------------------------------------------|：------------------------------------------------|：-------------------------------------||：-------8420518|response_part_0_script_0_|$[*].id|JSONPath|profileIds#|variable|TRUE8420518|response_part_0_script_1_|$[*].name|JSONPath|profileName#|variable|TRUE8420518|response_part_0_script_2_|$[*].environment|JSONPath|profileEnvironments#|variable|TRUE8420518|response_part_0_script_3_|$[*].description|JSONPath|profileDescription#|variable|TRUE

---

您可能会发现首先在子查询中解析更可读：

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

DB Fiddle演示

Answer 2

对我来说，这看起来像一个聚合：

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);

我要强调的是，这是基于你的样本数据。目前尚不清楚是否有更好的方法来分配前缀。一个想法是使用正则表达式：

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