Thank you. Using perl code to process the result sets is a solution, but I really want to know what is the reason for the failure and what parameter in DBI or MySQL control the result string length.

I like to let Database engine to do all the work if possible, esp. for any type of 'set' calculation, so that I don't need to process row by row. Your code can be simplified a bit with 'ROLLUP' in the query':

my $stmt = "SELECT prod, count(*) from prod GROUP BY prod WITH ROLLUP;
+";

$sth = $dbh->prepare( $stmt );
$sth->execute();

my %pivot;
while ( my ( $k, $v ) = $sth->fetchrow_array() ) {
   $k = 'Total' unless defined $k; 
   $k = 'Unknown' if $k eq '';
   $pivot{$k} = $v;
}
[download]