Обсуждение: Crosstab question
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
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