title: Multiple select queries on the same table in Oracle
body: <p>I have a table with all my data. I have tried joins, subqueries and unions, but none give me the desired result. I am running Oracle 12g.</p>
  
  <p>This is a sample table:</p>
  
  <pre><code>FILENAME | VALUE | RAW
  AA       | XX    | ZZ
  AA       | YY    | WW
  DD       | GG    | II
  DD       | HH    | JJ
  </code></pre>
  
  <p>The current SQL statement that I use is:</p>
  
  <pre><code>SELECT FILENAME, VALUE VALUE1 FROM TABLE WHERE FILENAME='AA';
  SELECT FILENAME, VALUE VALUE2 FROM TABLE WHERE FILENAME='DD';
  </code></pre>
  
  <p>And I get the following result:</p>
  
  <pre><code>FILENAME | VALUE1
  AA       | XX
  AA       | YY
  </code></pre>
  
  <p>and </p>
  
  <pre><code>FILENAME | VALUE2
  DD       | GG
  DD       | HH
  </code></pre>
  
  <p>but I need the result to be:</p>
  
  <pre><code>FILENAME | VALUE1 | VALUE2
  AA       | XX     | GG
  AA       | YY     | HH
  </code></pre>
  
  <p>It doesn't matter if AA or DD is in the FILENAME. There are no foreign keys or unique values that I can do a simple INNER JOIN. I am not able to add new columns to the table. I was hoping to do sort my data and then use Oracle's ROWNUM as a temp column which I could then use to do a SELF JOIN but Oracle won't let me.</p>
  
  <p>Example:</p>
  
  <pre><code>SELECT L.FILENAME, L.VALUE VALUE1, R.VALUE VALUE2
  FROM TABLE L JOIN TABLE R
  ON (L.ROWNUM=R.ROWNUM)
  WHERE L.FILENAME IN ('AA','DD');
  </code></pre>
  
  <p>Any help is appreciated.</p>