Crosstab question - Mailing list pgsql-sql
| From | Leendert Paul Diterwich |
|---|---|
| Subject | Crosstab question |
| Date | |
| Msg-id | 453b5c95.3a026987.2aae.ffffb959@mx.google.com Whole thread Raw |
| Responses |
Re: Crosstab question
(Michael Fuhr <mike@fuhr.org>)
|
| List | pgsql-sql |
<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>