[SQL] Crosstab question

[Leendert Paul Diterwich]

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 


   

Re: [SQL] Crosstab question

[Michael Fuhr]

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.80.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

---(end of broadcast)---
TIP 5: don't forget to increase your free space map settings