Using crosstab in tablefunc

Hi,

I have been playing with the crosstab functions in tablefunc, but have not
be getting what I was expecting.

Here is the SQL and result:

test=# select a.biomaterial_id, category, value from v_biosource_writeable
a, v_characteristics b where a.biomaterial_id = b.biomaterial_id and
(category = 'species' or category = 'strain' or category = 'genotype') order
by 1,2;

biomaterial_id | category | value
----------------+----------+--------------
32 | genotype | CQ sensitive
32 | species | P.falciparum
32 | strain | 3D7
33 | genotype | CQ sensitive
33 | species | P.falciparum
33 | strain | 3D7
34 | genotype | CQ sensitive
34 | species | P.falciparum
34 | strain | 3D7
(9 rows)

There are 3 categories..... When I use crosstab I get this

test=# select * from crosstab('select a.biomaterial_id, category, value from
v_biosource_writeable a, v_characteristics b where a.biomaterial_id =
b.biomaterial_id and (category = ''species'' or category = ''strain'' or
category = ''genotype'') order by 1,2;', 3) as ct(biomaterial_id int,
species text, strain text, genotype text);

biomaterial_id | species | strain | genotype
----------------+--------------+--------------+----------
32 | CQ sensitive | P.falciparum | 3D7
33 | CQ sensitive | P.falciparum | 3D7
34 | CQ sensitive | P.falciparum | 3D7

But this is not the right way around.... The values are not with the
appropriate categories....

Any ideas as to what I am doing wrong here?

Thanks for any help.

adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.
Mailscanner thanks transtec Computers for their support.