#!perl
use strict;
use DBI;
my $dbh = connect(...);
$dbh->do('SET SESSION group_concat_max_len = 2048');;

my $pvt_col = 'prod';
my $count   = 'count';
my $table   = 'prod_test';

#test data
create_test_db($ARGV[0] || 10);

my $s1 =<<S1;
'SUM(CASE WHEN $pvt_col IS NULL 
 THEN $count ELSE 0 END) AS Total,'
S1

my $s2 =<<S2;
CONCAT(
'(SUM(CASE WHEN $pvt_col = ''',$pvt_col,''' 
THEN $count ELSE 0 END)) AS ',$pvt_col
)
S2

my $sql1 = <<SQL1;
 SELECT CONCAT($s1,GROUP_CONCAT(DISTINCT $s2)) 
 FROM         
   ( SELECT $pvt_col,count(*) AS count
     FROM $table
     GROUP BY $pvt_col WITH ROLLUP) as temptbl
SQL1

my $tmp = $dbh->selectrow_array($sql1);

my $sql2 = <<SQL2;
 SELECT $tmp 
 FROM ( SELECT $pvt_col, count(*) AS $count
 FROM $table 
 GROUP BY $pvt_col WITH ROLLUP) as temptbl
SQL2

my @f = $dbh->selectrow_array($sql2);
print  "@f\n";
  
   
sub create_test_db {
  my $no = shift;
#$dbh->do("DROP TABLE $table");
  $dbh->do("CREATE TABLE IF NOT EXISTS $table (
            id       int(11)     NOT NULL AUTO_INCREMENT,
            $pvt_col varchar(20) NOT NULL,
            PRIMARY KEY (id))"); 

  $dbh->do("DELETE FROM $table");
  for my $n (1..$no){
    for (1..$n){
      $dbh->do("INSERT INTO $table VALUES (?,?)",undef,undef,sprintf "prod%02d",$n);
    }
  }
}