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 4D41BD1B501 for ; Mon, 20 Oct 2003 19:55:11 +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 96272-01 for ; Mon, 20 Oct 2003 16:54:41 -0300 (ADT) Received: from joeconway.com (66-146-172-86.skyriver.net [66.146.172.86]) by svr1.postgresql.org (Postfix) with ESMTP id 3D132D1B53D for ; Mon, 20 Oct 2003 16:54:39 -0300 (ADT) Received: from [206.19.64.3] (account jconway HELO joeconway.com) by joeconway.com (CommuniGate Pro SMTP 4.1.5) with ESMTP-TLS id 437521; Mon, 20 Oct 2003 12:53:31 -0700 Message-ID: <3F943CCE.5080800@joeconway.com> Date: Mon, 20 Oct 2003 12:51:42 -0700 From: Joe Conway User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.0; en-US; rv:1.3) Gecko/20030312 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Adam Witney Cc: pgsql-sql Subject: Re: Crosstab question References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200310/257 X-Sequence-Number: 15657 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