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.
---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
