title: How to select data from several rows in a single table in one result row?
body: <p>How to select multiple entries in a single query.</p>
  
  <p>The db table structure represents an excel like spreadsheet. Each observation is a workbook.  </p>
  
  <pre><code>Work book 1  
  1     2  
  3     4  
  
  Work book 2  
  5    6  
  7    8  
  </code></pre>
  
  <p>with each db row representing a single cell and observations in separate workbooks. 
  The table definition is:</p>
  
  <pre><code>observation integer 
  row_number integer
  col_number integer
  value integer
  </code></pre>
  
  <p>The database then looks like this: </p>
  
  <pre><code>observation row_number  col_number  value  
      1        1             1        1  
      1        1             2        2  
      1        2             1        3  
      1        2             2        4  
      2        1             1        5  
      2        1             2        6  
      2        2             1        7  
      2        2             2        8  
  </code></pre>
  
  <p>The question is how to create a single query to: </p>
  
  <pre><code>select observation, value as value1 from database where row_number = 1 and col_number = 2,
  select value as value2 from database where row_number = 2 and col_number = 1;
  </code></pre>
  
  <p>to create:</p>
  
  <pre><code>observation value1  value2  
  1             2      3  
  2             6      7 
  </code></pre>
  
  <p>I have tried joins and subqueries. </p>