将行值转换为列- oracle 11g

Question

我从XML类型的字段中提取一些数据，从特定的标记中获取一些值，并设法以这样的结果形式收集它们：

echo  | type| color             | active
-------------------------------------------
echo1 | car | yellow,green,blue | no,no,no
echo1 | car | yellow,green      | yes,yes
echo2 | car | green,blue,red    | no,no,no
echo2 | car | blue,red          | yes,yes
echo3 | car | yellow,green      | no,yes
...

现在，我需要将这些结果转换为：

echo  | type| color  | active
------------------------------
echo1 | car | yellow | no
echo1 | car | green  | no
echo1 | car | blue   | no
echo1 | car | yellow | yes
echo1 | car | green  | yes
echo2 | car | green  | no
echo2 | car | blue   | no
echo2 | car | red    | no
echo2 | car | blue   | yes
echo2 | car | red    | yes
echo3 | car | yellow | no
echo3 | car | green  | yes
...

也就是说，我需要同时从第三列和第四列中分割值。颜色列中的每个值在活动列中都有相应的值，我需要在记录中匹配这些对。

因此，有了这样的结果，我用以下方法：

SELECT  echo, type, trim(x.COLUMN_VALUE) color, trim(y.COLUMN_VALUE) active 
FROM 
( RESULTS )
, xmltable(('"' || REPLACE(color, ',', '","') || '"')) x, xmltable(('"' || REPLACE(active, ',', '","') || '"')) y

ORDER BY echo, type, color, active

如果我一次只转换这两列中的一列，它就会正确工作，但是当我想同时转换2时，它会将记录相乘，就像将y列的值乘以x列的值一样。

如果在最后一列中只有“是”或“否”值，那么它将在select中使用distinct，它将消除过时的(不是真)记录。但由于价值观参差不齐，我很难做到这一点。

有人能给我一个提示吗?如何修复它，以避免过时的记录？

提前感谢！

示例XML：

<rowCollection>
  <row>
    <column name="active">YES</column>
    <column name="customertype">Default</column>
    <column name="type">Default</column>
    <column name="risklevel">Default</column>
    <column name="color">yellow</column>
  </row>
  <row>
    <column name="active">YES</column>
    <column name="customertype">Default</column>
    <column name="type">Default</column>
    <column name="risklevel">Default</column>
    <column name="color">green</column>
  </row>
</rowCollection>

用于生成结果的查询片段：

xmlcast(
  xmlquery(
    'string-join(/rowCollection/row/column[5]/text(),",")'
    passing xml_field
    RETURNING CONTENT
  ) AS VARCHAR2(500)
) color,
xmlcast(
  xmlquery(
    'string-join(/rowCollection/row/column[1]/text(),",")'
    passing xml_field
    RETURNING CONTENT
  ) AS VARCHAR2(300)
) active

Answer 1

可以按以下方式使用hierarchy查询：

SQL>选择ECHO，类型为2 REGEXP_SUBSTR(颜色，'^，+'，1，COLUMN_VALUE)为颜色，3 REGEXP_SUBSTR( ACTIVE，'^，+'，1，COLUMN_VALUE)为ACTIVE 4，从结果5交叉连接表(CAST(从双7连接多集(6选择级别，按级别<= REGEXP_COUNT，') +1.8)作为SYS.ODCINUMBERLIST )；回声TYP颜色活动红色，是，echo3，黄色，没有echo3，汽车绿色，是，12行选择。SQL>

干杯！！