Crosstab question

Hi,

I am trying to figure out the use of crosstab(text sql, int N)

The sql I have is

cabbage=# select geneid, bioassay_id, sig_median from imagene order by 1,2;

geneid | bioassay_id | sig_median
---------------------------------+-------------+------------
16s rRNA (AP1A1) | 107 | 65535.0
16s rRNA (AP1A1) | 108 | 1904.0
16s rRNA (AP1A1) | 109 | 65535.0
16s rRNA (AP2A1) | 106 | 197.0
16s rRNA (AP2A1) | 108 | 197.0
16s rRNA (AP2A1) | 109 | 10525.0
16s rRNA (MWG1B1) | 106 | 49.0
16s rRNA (MWG1B1) | 107 | 282.0
16s rRNA (MWG1B1) | 108 | 49.0
16s rRNA (MWG1B1) | 109 | 282.0

However when I use a crosstab function like so

cabbage=# select * from crosstab('select geneid, bioassay_id, sig_median
from imagene order by 1,2;', 4) as ct(geneid text, b106 numeric, b107
numeric, b108 numeric, b109 numeric);

geneid | b106 | b107 | b108 | b109
---------------------------------+---------+---------+---------+---------
16s rRNA (AP1A1) | 65535.0 | 1904.0 | 65535.0 |
16s rRNA (AP2A1) | 197.0 | 197.0 | 10525.0 |
16s rRNA (MWG1B1) | 49.0 | 282.0 | 49.0 | 282.0

However it should be:

geneid | b106 | b107 | b108 | b109
---------------------------------+---------+---------+---------+---------
16s rRNA (AP1A1) | | 65535.0 | 1904.0 | 65535.0
16s rRNA (AP2A1) | 197.0 | | 197.0 | 10525.0
16s rRNA (MWG1B1) | 49.0 | 282.0 | 49.0 | 282.0

The missing values seemed to have been ignored and so the data is being
shifted to the left and so put in the wrong columns.

Am I using this function correctly? What is supposed to happen with missing
values?

Thanks for any help

Adam

--
This message has been scanned for viruses and
dangerous content by MailScanner, and is
believed to be clean.