Beefy Boxes and Bandwidth Generously Provided by pair Networks
Welcome to the Monastery
 
PerlMonks  

X, Y Table structure

by Anonymous Monk
on May 10, 2011 at 01:04 UTC ( #903867=perlquestion: print w/ replies, xml ) Need Help??
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
QTY12345
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 ).

Comment on X, Y Table structure
Re: X, Y Table structure
by ikegami (Pope) 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>");

      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.

Re: X, Y Table structure
by LanX (Canon) 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

    Cheers Rolf

      No it's not a homework, it's an actual work.

      Thanks for your input

Re: X, Y Table structure
by Anonymous Monk on May 10, 2011 at 10:59 UTC
Re: X, Y Table structure
by ww (Bishop) 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?
      FWIW, AFAIK, its temporary, for creating reports, like SQLite::VirtualTable::Pivot shows
      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.

      If you've seen my reply to ikegami, You can see that I've added IDs in my example.

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";

Log In?
Username:
Password:

What's my password?
Create A New User
Node Status?
node history
Node Type: perlquestion [id://903867]
Approved by ikegami
help
Chatterbox?
and the web crawler heard nothing...

How do I use this? | Other CB clients
Other Users?
Others exploiting the Monastery: (6)
As of 2014-10-02 01:05 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    What is your favourite meta-syntactic variable name?














    Results (41 votes), past polls