|
Anonymous Monk has asked for the wisdom of the Perl Monks concerning the following question:
I have the following database table structure:
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
I am looking to get this kind of output:
| |
Sizes |
| QTY | 1 | 2 | 3 | 4 | 5 |
| 100 | $43 | $45 | $50 | | |
| 200 | | | | $55 | |
| 250 | $52 | $55 | $56 | | $61 |
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.
But I couldn't imagine the data structure that could give me the output shown above.
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 ).
|
Re: X, Y Table structure
by ikegami (Patriarch) on May 10, 2011 at 01:15 UTC
|
You could avoid loading the entire table into memory by doing two reads («SELECT MAX(size)» and «SELECT qty, size, price ORDER BY qty ASC, size ASC»).
my $max_size = 5; # SELECT MAX(size)
print("<table>");
print("<tr>");
print("<th>");
print("<th>Sizes");
print("<tr>");
print("<th>QTY");
for my $size (1..$max_size) {
print("<th>$size");
}
my $last_qty;
my $last_size;
for (
# SELECT qty, size, price ORDER BY qty ASC, size ASC
[ 100, 1, 43 ],
[ 100, 2, 45 ],
[ 100, 3, 50 ],
[ 200, 4, 55 ],
[ 250, 1, 52 ],
[ 250, 2, 55 ],
[ 250, 3, 56 ],
[ 250, 5, 61 ],
) {
my ($size, $qty, $price) = @$_;
if (!defined($last_qty) || $qty != $last_qty) {
if (defined($last_size)) {
print("<td>") for $last_size+1 .. $max_size;
$last_size = 0;
}
print("<tr>");
print("<th>$qty");
$last_qty = $qty;
}
print("<td>") for $last_size+1 .. $size-1;
print("<td>\$$price");
}
if (defined($last_size)) {
print("<td>") for $last_size+1 .. $max_size;
}
print("</table>");
But it's simpler to just loading everything into memory.
use List::Util qw( max );
my %table;
my $max_size = 0;
for (
# SELECT qty, size, price
[ 100, 1, 43 ],
[ 250, 1, 52 ],
[ 100, 2, 45 ],
[ 250, 2, 55 ],
[ 100, 3, 50 ],
[ 250, 3, 56 ],
[ 200, 4, 55 ],
[ 250, 5, 61 ],
) {
my ($qty, $size, $price) = @$_;
$table{$qty}[$size] = $price;
$max_size = $size if $size > $max_size;
}
print("<table>");
print("<tr>");
print("<th>");
print("<th>Sizes");
print("<tr>");
print("<th>QTY");
for my $size (1..$max_size) {
print("<th>$size");
}
for my $qty (sort { $a <=> $b } keys(%table)) {
print("<tr>");
print("<th>$qty");
for my $size (1..$num_cols) {
print("<td>", defined($table{$qty}[$size]) ? "\$$table{$qty}[$si
+ze]" : '');
}
}
print("</table>");
| [reply]
[d/l]
[select] |
|
|
Thanks
The second example was very close to what I want, as sizes aren't always 1,2,3,4 there might be a skipped size some where so I had to alter it a little bit
Here's my modified code:
my %table;
my %sizes;
for (
# SELECT qty, size, price
[1, 100, 1, 43 ],
[2, 250, 1, 52 ],
[3, 100, 2, 45 ],
[4, 250, 2, 55 ],
[5, 100, 3, 50 ],
[6, 250, 3, 56 ],
[7, 200, 4, 55 ],
[8, 250, 5, 61 ],
) {
my ($id,$qty, $size, $price) = @$_;
$table{$qty}[$size] = { 'id' => $id, 'price' => $price };
$sizes{$size}++;
}
print("<table border='1'>");
print("<tr>");
print("<th></th>");
print("<th>Sizes</th>");
print("</tr>");
print("<tr>");
print("<th>QTY");
for my $size (sort {$a <=> $b } keys %sizes) {
print("<th>$size");
}
for my $qty (sort { $a <=> $b } keys(%table)) {
print("<tr>");
print("<th>$qty");
for my $size (sort {$a <=> $b } keys %sizes) {
print("<td>", defined($table{$qty}[$size]) ? "<a href='$table{$q
+ty}[$size]->{id}'>\$$table{$qty}[$size]->{price}</a>" : '');
}
}
print("</table>");
Please give me your thoughts
Thanks in advance. | [reply]
[d/l] |
Re: X, Y Table structure
by LanX (Saint) on May 10, 2011 at 01:39 UTC
|
HI
sorry, I'm tired and it smells like homework...
So here only a stub for you to work on:
(Showing how to use a HoH to represent a table)
use strict;
use Data::Dumper;
my (%h,%x,%y);
scalar <DATA>;
while(<DATA>){
chomp;
my ($id, $size, $qty, $price)= split /\s*,\s*/,$_ ;
$h{$qty}{$size}=$price;
$y{$qty}=1;
$x{$size}=1;
}
for my $y (sort keys %y) {
for my $x (sort keys %x) {
if (exists $h{$y}{$x}) {
print "$h{$y}{$x}\t";
}else {
print "\t"
}
}
print "\n";
}
__DATA__
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
OUTPUT:
43 45 50
55
52 55 56 61
| [reply]
[d/l]
[select] |
|
|
| [reply] |
|
|
| [reply] |
|
|
Re: X, Y Table structure
by Anonymous Monk on May 10, 2011 at 10:59 UTC
|
| [reply] |
Re: X, Y Table structure
by ww (Archbishop) on May 10, 2011 at 12:17 UTC
|
Just out of curiousity, what use is a transformation such as you show, leaving a table with data that no longer has any link to its IDs? | [reply] |
|
|
| [reply] |
|
|
Given this table :
Student Subject Grade
------- ------- -----
Joe Reading A
Joe Writing B
Joe Arithmetic C
Mary Reading B-
Mary Writing A+
Mary Arithmetic C+
A pivot table created using the columns "Student" and "Subject" and th
+e value "Grade" would yield :
Student Arithmetic Reading Writing
------- ---------- ------- ----------
Joe C A B
Mary C+ B- A+
Because I forgot all about VIEWS/PIVOT, I did the transformation manually. | [reply]
[d/l] |
Re: X, Y Table structure
by wind (Priest) on May 10, 2011 at 17:30 UTC
|
use strict;
use warnings;
my %table;
my %sizes;
for (
# SELECT qty, size, price
[ 100, 1, 43 ],
[ 250, 1, 52 ],
[ 100, 2, 45 ],
[ 250, 2, 55 ],
[ 100, 3, 50 ],
[ 250, 3, 56 ],
[ 200, 4, 55 ],
[ 250, 5, 61 ],
) {
my ($qty, $size, $price) = @$_;
$table{$qty}{$size} = $price;
$sizes{$size} = 1;
}
my @sizes = sort {$a <=> $b} keys %sizes;
print '<table>', "\n";
print '<tr>';
print '<th> </th>';
print '<th colspan=' . @sizes . '>Sizes</th>';
print '</tr>', "\n";
print '<tr>';
print '<th>QTY</th>';
for my $size (@sizes) {
print "<th>$size</th>";
}
for my $qty (sort { $a <=> $b } keys %table) {
print '<tr>';
print "<th>$qty</th>";
for my $size (@sizes) {
print '<td>', (defined $table{$qty}{$size} ? "\$$table{$qty}{$si
+ze}" : ' '), '</td>';
}
print '</tr>', "\n";
}
print '</table>', "\n";
| [reply]
[d/l] |
|
|
