X-Original-To: pgsql-sql-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id 064BAD1B4E3 for ; Mon, 20 Oct 2003 18:42:41 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 82159-07 for ; Mon, 20 Oct 2003 15:42:11 -0300 (ADT) Received: from mailhub1.sghms.ac.uk (firewall.sghms.ac.uk [194.82.50.2]) by svr1.postgresql.org (Postfix) with ESMTP id EEA22D1B508 for ; Mon, 20 Oct 2003 15:42:08 -0300 (ADT) Received: from [194.82.51.24] (helo=imail) by mailhub1.sghms.ac.uk with esmtp (Exim 4.24) id 1ABeye-0002Fk-MY for pgsql-sql@postgresql.org; Mon, 20 Oct 2003 19:42:00 +0100 Received: from [172.16.20.3] (mrc1-003.sghms.ac.uk [172.16.20.3]) by imail.sghms.ac.uk (iPlanet Messaging Server 5.1 HotFix 0.7 (built May 7 2002)) with ESMTPA id <0HN200H6JJY0KT@imail.sghms.ac.uk> for pgsql-sql@postgresql.org; Mon, 20 Oct 2003 19:42:00 +0100 (BST) Date: Mon, 20 Oct 2003 19:40:42 +0100 From: Adam Witney Subject: Crosstab question To: pgsql-sql Message-id: MIME-version: 1.0 Content-type: text/plain; charset=US-ASCII Content-transfer-encoding: 7BIT User-Agent: Microsoft-Entourage/10.1.1.2418 X-MailScanner-Information: Please contact sysadmin at sghms.ac.uk for more information X-MailScanner-MH1: Found to be clean X-MailScanner-SpamCheck: not spam, SpamAssassin (score=-4.4, required 5, APARENTLY_FROM_MYSELF 1.00, BAYES_00 -4.90, FROM_SGHMS -1.00, MY_NO_QU 0.50) X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/251 X-Sequence-Number: 15651 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.