Re: Using crosstab in tablefunc

Adam Witney wrote:
>  biomaterial_id | category |    value
> ----------------+----------+--------------
>              32 | genotype | CQ sensitive
>              32 | species  | P.falciparum
>              32 | strain   | 3D7

[...snip...]

>
> 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);

You asked for the categories to be named incorrectly, I think. If the
categories are genotype, species, and strain in that order, then just name the
crosstab columns as such:

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, genotype text, species text, strain text);
                           ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

I.e. the categories will be in the order provided by the query submitted to
crosstab. The names you give the categories in the AS column reference clause
are completely arbitrary. You could just as easily substitute:
   AS ct(f1 int, c1 text, c2 text, c3 text);

HTH,

Joe