perlquestion
Anonymous Monk
<p>I have the following database table structure:</p>
<pre>
id, size, qty, price
1 , 1 , 100, 43
2 , 1 , 250, 52
4 , 2 , 100, 45
5 , 2 , 250, 55
6 , 3 , 100, 50
7 , 3 , 250, 56
8 , 4 , 200, 55
9 , 5 , 250, 61
</pre>
<p>I am looking to get this kind of output:</p>
<table border="1">
<tr>
<td> </td>
<td align="center" colspan="5">Sizes</td>
</tr>
<tr>
<td>QTY</td><td>1</td><td>2</td><td>3</td><td>4</td><td>5</td>
</tr>
<tr>
<td>100</td><td>$43</td><td>$45</td><td>$50</td><td> </td><td> </td>
</tr>
<tr>
<td>200</td><td> </td><td> </td><td> </td><td>$55</td><td> </td>
</tr>
<tr>
<td>250</td><td>$52</td><td>$55</td><td>$56</td><td> </td><td>$61</td>
</tr>
</table>
<p>My thought that this requires three sql selects,
first two to get distinct values of sizes, qty and the 3rd to build data structure out of it.
</p>
<p>But I couldn't imagine the data structure that could give me the output shown above.</p>
<p>Therefore I need your help on the most appropriate solution for this ( the data structure that works best & if this can be done in one sql query ).</p>
3