pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedCrosstab question
4+ messages / 4 participants
[nested] [flat]
* Crosstab question
@ 2003-10-20 18:40 Adam Witney <awitney@sghms.ac.uk>
2003-10-20 19:51 ` Re: Crosstab question Joe Conway <mail@joeconway.com>
0 siblings, 1 reply; 4+ messages in thread
From: Adam Witney @ 2003-10-20 18:40 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crosstab question
2003-10-20 18:40 Crosstab question Adam Witney <awitney@sghms.ac.uk>
@ 2003-10-20 19:51 ` Joe Conway <mail@joeconway.com>
0 siblings, 0 replies; 4+ messages in thread
From: Joe Conway @ 2003-10-20 19:51 UTC (permalink / raw)
To: Adam Witney <awitney@sghms.ac.uk>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Crosstab question
@ 2006-10-22 11:57 Leendert Paul Diterwich <paul@winaskon.nl>
2006-10-22 15:45 ` Re: Crosstab question Michael Fuhr <mike@fuhr.org>
0 siblings, 1 reply; 4+ messages in thread
From: Leendert Paul Diterwich @ 2006-10-22 11:57 UTC (permalink / raw)
To: pgsql-sql
Dear list,
I recently discovered the crosstab function in postgresql.
What I'm trying to accomplish is turn this:
Suppliernumber Productnumber Price
0 2 0.8
0 3 1.2
1 2 0.9
1 3 1.1
Into this:
Productnumber Supplier1 Supplier2
2 0.8 0.9
3 1.2 1.1
The problem is that the data is being shifted when there is no
price for Supplier1 for product 3:
Productnumber Supplier1 Supplier2
2 0.8 0.9
3 1.1(WRONG!)
I'm using this command to retrieve the data:
select * from crosstab('select int_artnr, int_crednr, flt_inkoopprijs from
artcredinkoopprijzen where
int_crednr = 2 OR int_crednr = 3') as c(int_artnr integer, supp1 float4,
supp2
float4);
Am I doing something wrong or is this a limitation of the function?
With kind regards,
Paul
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Crosstab question
2006-10-22 11:57 Crosstab question Leendert Paul Diterwich <paul@winaskon.nl>
@ 2006-10-22 15:45 ` Michael Fuhr <mike@fuhr.org>
0 siblings, 0 replies; 4+ messages in thread
From: Michael Fuhr @ 2006-10-22 15:45 UTC (permalink / raw)
To: Leendert Paul Diterwich <paul@winaskon.nl>; +Cc: pgsql-sql
On Sun, Oct 22, 2006 at 01:57:09PM +0200, Leendert Paul Diterwich wrote:
> The problem is that the data is being shifted when there is no
> price for Supplier1 for product 3:
>
> Productnumber Supplier1 Supplier2
> 2 0.8 0.9
> 3 1.1(WRONG!)
>
> I'm using this command to retrieve the data:
>
> select * from crosstab('select int_artnr, int_crednr, flt_inkoopprijs from
> artcredinkoopprijzen where
> int_crednr = 2 OR int_crednr = 3') as c(int_artnr integer, supp1 float4,
> supp2 float4);
Try using the crosstab(text source_sql, text category_sql) variant.
Unrelated suggestion: prices should probably be numeric instead of
floating-point due to the inexactness of the latter.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2006-10-22 15:45 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-10-20 18:40 Crosstab question Adam Witney <awitney@sghms.ac.uk>
2003-10-20 19:51 ` Joe Conway <mail@joeconway.com>
2006-10-22 11:57 Crosstab question Leendert Paul Diterwich <paul@winaskon.nl>
2006-10-22 15:45 ` Michael Fuhr <mike@fuhr.org>
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