title: Using case statements to chop up a table
body: <p>I have a table stored in postgres which looks like this:</p>
  
  <pre><code>Column    RowIdx    Value
  1         0         Dr A
  1         1         Mr B
  1         2         Mrs C
  2         0         101
  2         1         105
  2         2         127
  3         0         Red
  3         1         Green
  3         2         Blue
  4         0         Miss D
  4         1         Mr E
  4         2         Ms F
  5         0         23
  5         1         197
  5         2         256
  6         0         Black
  6         1         Brown
  6         2         Yellow
  </code></pre>
  
  <p>When I want to extract a table that looks like this:</p>
  
  <pre><code>Name    HumanID    FavouriteColor
  Dr A    101        Red
  Mr B    105        Green
  Mrs C   127        Blue
  </code></pre>
  
  <p>I use the following SQL statement:</p>
  
  <pre><code>SELECT 
      max(CASE WHEN Column=1 THEN Value ELSE null END) AS Name,
      max(CASE WHEN Column=2 THEN Value ELSE null END) AS HumanID,
      max(CASE WHEN Column=3 THEN Value ELSE null END) AS FavouriteColor
  
  WHERE (Column=1 OR Column=2 OR Column=3) 
  
  GROUP BY RowIdx 
  ORDER BY RowIdx
  </code></pre>
  
  <p>This works perfectly fine.  I now want to export a table that looks like this:</p>
  
  <pre><code>Name    HumanID    FavouriteColor
  Dr A    101        Red
  Mr B    105        Green
  Mrs C   127        Blue
  Miss D  23         Black
  Mr E    197        Brown
  Mrs C   256        Yellow
  </code></pre>
  
  <p>I modified my SQL statement to this:</p>
  
  <pre><code>SELECT 
      max(CASE WHEN (Column=1 OR Column=4) THEN Value ELSE null END) AS Name,
      max(CASE WHEN (Column=2 OR Column=5) THEN Value ELSE null END) AS HumanID,
      max(CASE WHEN (Column=3 OR Column=6) THEN Value ELSE null END) AS FavouriteColor
  
  WHERE (Column=1 OR Column=2 OR Column=3 OR Column=4 OR Column=5 OR Column=6) 
  
  GROUP BY RowIdx 
  ORDER BY RowIdx
  </code></pre>
  
  <p>However this doesn't seem to work, I'm just getting the first table again.  I know enough sql to get by, but I don't really understand what order the statements are being evaluated in, which is making it hard to figure out why I'm getting the result that I am.</p>
  
  <p>Can anyone shed some light on this?</p>
  
  <p>EDIT: Just to give some more context.  My system holds hundreds of thousands of csv files.  It does this by 'stacking' the columns of a csv file into a single column, then inserting it into the table.  I can guarantee that ordering of columns/cells that come from the same csv file, but I cannot guarantee it between csv files.  The example I gave above represents two csv files that have been imported.  Each file contains information about a person.  The operation I'm trying to implement would allow my to merge the two csv files.</p>
  
  <p>I can achieve the result by exporting one table, then exporting the other, and then copying the right bits together.  I'd like to do it through this SQL statement since I think it's more efficient (due to other constraints within the system).</p>