DBIx::SQLCrosstab 1.17

DBIx::SQLCrosstab Perl module creates a server-side cross tabulation from a database.

SYNOPSIS

use DBIx::SQLCrosstab;
my $dbh=DBI->connect("dbi:driver:database"
"user","password", {RaiseError=>1})
or die "error in connection $DBI::errstrn";

my $params = {
dbh => $dbh,
op => [ [SUM, salary] ],
from => person INNER JOIN departments USING (dept_id),
rows => [
{ col => country},
],
cols => [
{
id => dept,
value =>department,
from =>departments
},
{
id => gender, from => person
}
]
};
my $xtab = DBIx::SQLCrosstab->new($params)
or die "error in creation ($DBIx::SQLCrosstab::errstr)n";

my $query = $xtab->get_query("#")
or die "error in query building $DBIx::SQLCrosstab::errstrn";

# use the query or let the module do the dirty job for you
my $recs = $xtab->get_recs
or die "error in execution $DBIx::SQLCrosstab::errstrn";

# do something with records, or use the child class
# DBIx::SQLCrosstab::Format to produce well
# formatted HTML or XML output
#

my $xtab = DBIx::SQLCrosstab::Format->new($params)
or die "error in creation ($DBIx::SQLCrosstab::errstr)n";
if ($xtab->get_query and $xtab->get_recs) {
print $xtab->as_html;
my $xml_data = $xtab->as_xml;
}

DBIx::SQLCrosstab produces a SQL query to interrogate a database and generate a cross-tabulation report. The amount of parameters needed to achieve the result is kept to a minimum. You need to indicate which columns and rows to cross and from which table(s) they should be taken.

Acting on your info, DBIx::SQLCrosstab creates an appropriate query to get the desired result. Compared to spreadsheet based cross-tabulations, DBIx::SQLCrosstab has two distinct advantages, i.e. it keeps the query in the database work space, fully exploiting the engine capabilities, and does not limit the data extraction to one table.