generate a cross-tabulation report

[E.Elavazhagan]

Hi,

Please have a glance on my script and guide me the right way to obtain the
output.

The following code generates the normal display ie 3 columns and 4 rows.
Now I want to dispaly the  output like this i.e, unique first column and
rest are same.


  ERROR_CODE ID1 NAME1  ID2 NAME2  ID3 NAME3  ID4 NAME4


I tried to use the module DBIx::SQLCrosstab,but haven't got better
solution.Please advise me for the desired display.

Thanks,
Nee.




##########

#!/usr/local/perl-5.6.1/bin/perl

use strict;
use COPS;
use CGI qw(:standard);
use CGI::Cookie;
use POSIX;
use okcdefines;
use CGI::Carp qw(fatalsToBrowser);
#For DB connectivity
require "OVAL.pl";
my $cgi=new CGI;

my $JSCRIPT=<<EOF;
 window.onload = function(){
var myTable = document.getElementById('merge');
var rows = myTable.getElementsByTagName('tr');
var numRows = rows.length;
var numRowSpan=1;

for (var j = 1; j <(numRows-1); j++) {
if (numRowSpan<=1) {
var currentRow = myTable.getElementsByTagName('tr')[j];
var currentCell= currentRow.getElementsByTagName('td')[0];  // the 1st
column
var currentCellData = currentCell.childNodes[0].data;
}

if (j<numRows-1) {
if (myTable.getElementsByTagName('tr')[j+1]) {
var nextRow = myTable.getElementsByTagName('tr')[j+1];
var nextCell = nextRow.getElementsByTagName('td')[0];
var nextCellData = nextCell.childNodes[0].data;

// compare the current cell and the next cell
if (currentCellData == nextCellData) {
numRowSpan += 1;
currentCell.rowSpan = numRowSpan;
nextCell.style.display = 'none';   //disappear the next cell
} else {
numRowSpan = 1;
}
}
}
}
}
EOF
;

my $dbh = dbOvalconnect();


print  header,
        -start_html(
        -title => "COMPARE",
        -script=> $JSCRIPT
    );
generate_form();
print end_html();

sub generate_form {
my $sql1=qq{select OVAL_ERROR_COUNT.ERROR_CODE,OVAL_ERROR_COUNT.PAYER_ID,
OVAL_PAYER.NAME from  OVAL_PAYER,OVAL_ERROR_COUNT where
OVAL_ERROR_COUNT.PAYER_ID= OVAL_PAYER.PAYERID order by
OVAL_ERROR_COUNT.ERROR_CODE ASC };
#print "SQL QUERRY:$sql1\n";
my $sth = $dbh->prepare( $sql1 );
$sth->execute();
my( $error_code,$payer_id,$payer_name );
$sth->bind_columns( undef, \$error_code ,\$payer_id,\$payer_name );
while( $sth->fetch() ) {

 print qq~


   <table id='merge' border=1 cellspacing='0' class='form'
style="width:90%;">
  <tr><td rowspan=10 width='10%' valign='middle'
align='center'>$error_code</td>
      <td width='10%' valign='middle' align='center'>$payer_id</td>
               <td width='10%' valign='middle'
align='center'>$payer_name</td>

   </tr>
   </table>

~;
}
}



###########