From: Adam Witney <awitney@sghms.ac.uk>
To: pgsql-sql <pgsql-sql@postgresql.org>
Subject: Crosstab question
Date: Mon, 20 Oct 2003 19:40:42 +0100
Message-ID: <BBB9EABA.26A4A%awitney@sghms.ac.uk> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: awitney@sghms.ac.uk
Subject: Re: Crosstab question
In-Reply-To: <BBB9EABA.26A4A%awitney@sghms.ac.uk>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox