Thread: Crosstab question
<div class="Section1"><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Dear list,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I recently discovered the crosstab function in postgresql.</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">What I’m trying to accomplish is turn this:</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Suppliernumber Productnumber Price</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">0 2 0.8</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">0 3 1.2</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">1 2 0.9</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">1 3 1.1</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Into this:</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Productnumber Supplier1 Supplier2</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">2 0.8 0.9</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">3 1.2 1.1</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">The problem is that the data is being shifted when there is no<br /> price for Supplier1 for product 3:</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Productnumber Supplier1 Supplier2</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">2 0.8 0.9</span></font><p class="MsoNormal"><font face="Arial"size="2"><span style="font-size:10.0pt; font-family:Arial">3 1.1(WRONG!)</span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">I’m using this command to retrieve the data: </span></font><p class="MsoNormal"><font face="Arial" size="2"><spanstyle="font-size:10.0pt; font-family:Arial">select * from crosstab('select int_artnr, int_crednr, flt_inkoopprijs from artcredinkoopprijzen where</span></font><pclass="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">int_crednr = 2 OR int_crednr = 3') as c(int_artnr integer, supp1 float4, supp2</span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">float4); </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Am I doing something wrong or is this a limitation of the function? </span></font><p class="MsoNormal"><fontface="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">With kind regards,</span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial">Paul </span></font><p class="MsoNormal"><font face="Arial" size="2"><span style="font-size:10.0pt; font-family:Arial"> </span></font></div>
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