title: Convert rows values into columns - oracle 11g
body: <p>I'm extracting some data from XML type of fields, getting some values from specific tags, and I managed to gather them in a form of such results:</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>Now I need to convert those results into this:</p>
  
  <pre><code>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
  ...
  </code></pre>
  
  <p>meaning, I need to split values from the 3rd and 4th columns at once. Each of the values from color column has a corresponding value in the active column and I need to match those pairs in records.</p>
  
  <p>So having such results I use this:</p>
  
  <pre><code>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
  </code></pre>
  
  <p>It works correctly if I convert only one of those two columns at once, but when I want to convert 2 at once, it multiplies the records, as if multiplying values from column y by values from column x.</p>
  
  <p>If in the last column there were only "yes" or only "no" values, then it would work to use the distinct in the select, it would eliminate the obsolete (not true) records. But with mixed values I'm struggling to do it.</p>
  
  <p>Can anyone give me a tip how to fix it to not get obsolete records?</p>
  
  <p>Thanks in advance!</p>
  
  <hr>
  
  <p>Sample XML:</p>
  
  <pre class="lang-xml prettyprint-override"><code>&lt;rowCollection&gt;
    &lt;row&gt;
      &lt;column name="active"&gt;YES&lt;/column&gt;
      &lt;column name="customertype"&gt;Default&lt;/column&gt;
      &lt;column name="type"&gt;Default&lt;/column&gt;
      &lt;column name="risklevel"&gt;Default&lt;/column&gt;
      &lt;column name="color"&gt;yellow&lt;/column&gt;
    &lt;/row&gt;
    &lt;row&gt;
      &lt;column name="active"&gt;YES&lt;/column&gt;
      &lt;column name="customertype"&gt;Default&lt;/column&gt;
      &lt;column name="type"&gt;Default&lt;/column&gt;
      &lt;column name="risklevel"&gt;Default&lt;/column&gt;
      &lt;column name="color"&gt;green&lt;/column&gt;
    &lt;/row&gt;
  &lt;/rowCollection&gt;
  </code></pre>
  
  <p>Snippet of query used to generate results:</p>
  
  <pre><code>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
  </code></pre>