Thread: 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.016srRNA (AP1A1)                |         108 |     1904.016s rRNA (AP1A1)                |         109 |
65535.016srRNA (AP2A1)                |         106 |      197.016s rRNA (AP2A1)                |         108 |
197.016srRNA (AP2A1)                |         109 |    10525.016s rRNA (MWG1B1)               |         106 |
49.016srRNA (MWG1B1)               |         107 |      282.016s rRNA (MWG1B1)               |         108 |
49.016srRNA (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.016s rRNA (AP2A1)                |   197.0 |         |   197.0 | 10525.016s 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.

Adam Witney wrote:
> 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?

Yeah, that's a limitation of the version of crosstab distributed with 
7.3. And (believe it or not) I've actually found cases where that 
behavior is useful. There is another version of crosstab ("hashed" 
crosstab) that treats missing values the way you expect. You can grab a 
copy backported for 7.3 here:  http://www.joeconway.com/
You want the one that says: "contrib/tablefunc backported for PostgreSQL 
7.3.x -- sync'd with CVS HEAD 02-Oct-2003"

HTH,

Joe