correct way to create a pivot table in postgresql using CASE WHEN
- by mojones
I am trying to create a pivot table type view in postgresql and am nearly there! Here is the basic query:
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';
And the data:
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
What I am trying to get is the following:
acc | species | phylum
AJ012531 | Paromalostomum fusculum | Platyhelminthes
I am trying to do this with CASE WHEN, so I've got as far as the following:
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';
Which gives me the output:
acc | species | phylum
----------+-------------------------+-----------------
AJ012531 | Paromalostomum fusculum |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | | Platyhelminthes
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
AJ012531 | |
Now I know that I have to group by acc at some point, so I try
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;
But I get the dreaded
ERROR: column "tax_node.rank" must appear in the GROUP BY clause or be used in an aggregate function
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():
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;
but get the error:
ERROR: function first(character varying) does not exist
Can anyone offer any hints?
