Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id 8E0C09FB30F for ; Sun, 22 Oct 2006 08:57:28 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 04235-08 for ; Sun, 22 Oct 2006 08:57:12 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 5C8009FB31F for ; Sun, 22 Oct 2006 08:57:11 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id 23so932815ugr for ; Sun, 22 Oct 2006 04:57:09 -0700 (PDT) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:from:to:subject:date:mime-version:content-type:x-mailer:x-mimeole:thread-index:sender:message-id; b=BpanBsA6qCqwYDrSk71b6Um7QqB/abqNDYjsiJOZWMgDcrFIq0dTE4tu4MFkrjxrel816T/XHccjPWgWEIn4W0yDYahscHggNy+gSuuTlqqzt53Ldoik/lxd5yT0VlTTJWoz8jZGkAunLCkSK2gF8IURiDIQIWWKe0DdnDpCiW0= Received: by 10.66.243.4 with SMTP id q4mr3827038ugh; Sun, 22 Oct 2006 04:57:09 -0700 (PDT) Received: from pollux ( [80.56.53.77]) by mx.google.com with ESMTP id 34sm941808uga.2006.10.22.04.57.09; Sun, 22 Oct 2006 04:57:09 -0700 (PDT) From: "Leendert Paul Diterwich" To: Subject: Crosstab question Date: Sun, 22 Oct 2006 13:57:09 +0200 MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0012_01C6F5E1.F7A65E40" X-Mailer: Microsoft Office Outlook, Build 11.0.5510 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 Thread-Index: Acb0/NvQlazxk0hEROyqdD5XGN0ktwAAK+dQADTm5sA= Message-ID: <453b5c95.3a026987.2aae.ffffb959@mx.google.com> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=2.121 tagged_above=0 required=5 tests=AWL, HTML_90_100, HTML_MESSAGE, SPF_HELO_SOFTFAIL, SPF_SOFTFAIL X-Spam-Level: ** X-Archive-Number: 200610/197 X-Sequence-Number: 26586 This is a multi-part message in MIME format. ------=_NextPart_000_0012_01C6F5E1.F7A65E40 Content-Type: text/plain; charset="US-ASCII" Content-Transfer-Encoding: 7bit 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 ------=_NextPart_000_0012_01C6F5E1.F7A65E40 Content-Type: text/html; charset="US-ASCII" Content-Transfer-Encoding: quoted-printable

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 &nbs= p; 0.9

3 = ; = 1.2 &nbs= p; 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 &nbs= p; 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 =3D 2 OR int_crednr =3D 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 &n= bsp;

= &= nbsp;

------=_NextPart_000_0012_01C6F5E1.F7A65E40--