Thread: 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.

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