title: correct way to create a pivot table in postgresql using CASE WHEN
body: <p>I am trying to create a pivot table type view in postgresql and am nearly there!  Here is the basic query:</p>
  
  <pre><code>select 
  acc2tax_node.acc, tax_node.name, tax_node.rank 
  from 
  tax_node, acc2tax_node 
  where 
  tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';
  </code></pre>
  
  <p>And the data:</p>
  
  <pre><code>   acc    |          name           |     rank     
  ----------+-------------------------+--------------
   AJ012531 | Paromalostomum fusculum | species
   AJ012531 | Paromalostomum          | genus
   AJ012531 | Macrostomidae           | family
   AJ012531 | Macrostomida            | order
   AJ012531 | Macrostomorpha          | no rank
   AJ012531 | Turbellaria             | class
   AJ012531 | Platyhelminthes         | phylum
   AJ012531 | Acoelomata              | no rank
   AJ012531 | Bilateria               | no rank
   AJ012531 | Eumetazoa               | no rank
   AJ012531 | Metazoa                 | kingdom
   AJ012531 | Fungi/Metazoa group     | no rank
   AJ012531 | Eukaryota               | superkingdom
   AJ012531 | cellular organisms      | no rank
  </code></pre>
  
  <p>What I am trying to get is the following:</p>
  
  <pre><code>acc      | species                  | phylum
  AJ012531 | Paromalostomum fusculum  | Platyhelminthes
  </code></pre>
  
  <p>I am trying to do this with CASE WHEN, so I've got as far as the following:</p>
  
  <pre><code>select 
  acc2tax_node.acc, 
  CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as species, 
  CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as phylum 
  from 
  tax_node, acc2tax_node 
  where 
  tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531';
  </code></pre>
  
  <p>Which gives me the output:</p>
  
  <pre><code>   acc    |         species         |     phylum      
  ----------+-------------------------+-----------------
   AJ012531 | Paromalostomum fusculum | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | Platyhelminthes
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
   AJ012531 |                         | 
  </code></pre>
  
  <p>Now I know that I have to group by acc at some point, so I try </p>
  
  <pre><code>select 
  acc2tax_node.acc, 
  CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END as sp, 
  CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END as ph 
  from 
  tax_node, acc2tax_node 
  where 
  tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' 
  group by acc2tax_node.acc;
  </code></pre>
  
  <p>But I get the dreaded </p>
  
  <pre><code>ERROR:  column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function
  </code></pre>
  
  <p>All the previous examples I've been able to find use something like SUM() around the CASE statements, so I guess that is the aggregate function.  I have tried using FIRST():</p>
  
  <pre><code>select 
  acc2tax_node.acc, 
  FIRST(CASE tax_node.rank WHEN 'species' THEN tax_node.name ELSE NULL END) as sp, 
  FIRST(CASE tax_node.rank WHEN 'phylum' THEN tax_node.name ELSE NULL END) as ph 
  from tax_node, acc2tax_node where tax_node.taxid=acc2tax_node.taxid and acc2tax_node.acc='AJ012531' group by acc2tax_node.acc;
  </code></pre>
  
  <p>but get the error:</p>
  
  <pre><code>ERROR:  function first(character varying) does not exist
  </code></pre>
  
  <p>Can anyone offer any hints?</p>