Beefy Boxes and Bandwidth Generously Provided by pair Networks
Clear questions and runnable code
get the best and fastest answer
 
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
Replies are listed 'Best First'.
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?

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

      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.
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 browsing the Monastery: (8)
As of 2015-07-08 08:26 GMT
Sections?
Information?
Find Nodes?
Leftovers?
    Voting Booth?

    The top three priorities of my open tasks are (in descending order of likelihood to be worked on) ...









    Results (96 votes), past polls