++ed by:

1 PAUSE user
1 non-PAUSE user.

Giuseppe Maxia


DBIx::SQLCrosstab::Format - Formats results created by DBIx::SQLCrosstab


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

    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::Format->new($params)
        or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

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

    if ( $xtab->get_recs) {
        # do something with records, or use a built-in function
        # to produce a well formatted HTML table
        print $xtab->as_html;

        print $xtab->as_xml;
        print $xtab->as_yaml;
        print $xtab->as_csv('header');
        use Data::Dumper;
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('hoh')],
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('losh')],
        print Data::Dumper->Dump ([ $xtab->as_perl_struct('loh')],
    else {
        die "error in execution $DBIx::SQLCrosstab::errstr\n";


DBIx::SQLCrosstab::Format is a class descending from DBIx::SQLCrosstab. Being a child class, it inherits its parent methods and can be used in the same way.

In addition, it provides methods to produce formatted output.

Class methods


See DBIx::SQLCrosstab docs for usage and a detailed list of parameters


Returns a formatted HTML table with headers and values properly inserted, or undef on failure.


Returns an XML document containing the whole recordset properly tagged in tree format, or undef on failure.


Creates a MS Excel spreadsheet using Spreadsheet::WriteExcel. Requires a filename (or "-" for stdout).


Returns the recordset as a Perl structure. $mode is one of the following: - lol List of lists - losh List of simple hashes (one key per column) - loh List of hashes, tree-like, with an appropriate tree for each row - hoh Hash of hashes. The resultset as a tree (useful to pass to either XML::Simple or YAML)


Returns the recordset in YAML format. You must have YAML installed for this method to work.


Returns a text of Comma Separated Values, where each value is surronded by double quotes (text) or bare (numbers). If a true value is passed as $header parameter, the first row contains the list of column names, properly quoted and escaped.

Class attributes

In addition to the attributes available in DBIx::SQLCrosstab, the folowing ones become available in this class. They may be useful if you want to implement your own output methods.

Extending DBIx::SQLCrosstab::Format

The appropriate way of extending this class is through inheritance. Just create a descendant of DBIx::SQLCrosstab::Format and implement your new methods. The attributes with the relevant information become available after a call to the private method _find_headers().

The path to extension is something like the following.

First, create a new module:

 package DBIx::SQLCrosstab::Format::Extended;
 use DBI;
 use DBIx::SQLCrosstab;

 our $VERSION = '0.1';
 require Exporter;
 our @ISA= qw(DBIx::SQLCrosstab::Format);
 our @EXPORT=qw();
 our @EXPORT_OK=qw();

 sub as_myformat {
    my $self = shift;
    return undef unless $self->_find_headers();
    my $new_format = 
    return $new_format;

 sub do_something_smart_with {
    my $recs_tree = shift;
    my $header_formats = shift;
    # show off your skills here


Then, use the new module as you would use the parent one.

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

    my $xtab = DBIx::SQLCrosstab::Format::Extended->new($params)
        or die "error in creation ($DBIx::SQLCrosstab::errstr)\n";

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

    if ( $xtab->get_recs) {
        print $xtab->as_myformat;

Contains a reference to an array of arrays, one for each level of headers. Each cell is described with a hash containig name, colspan and rowspan values. Available after a call to _find_headers().


Contains a refernce to a hash descrbing the structure of the row level. Each level contains a list of fields and relative rowspans. Available after a call to _find_headers().


Contains a Tree::DAG_Node object with the structure of the column headers. Available after a call to _find_headers().


Contains a Tree::DAG_Node object with the structure of the row headers. Available after a call to _find_headers().



An article at OnLamp, "Generating Database Server-Side Cross Tabulations" (http://www.onlamp.com/pub/a/onlamp/2003/12/04/crosstabs.html) and one at PerlMonks, "SQL Crosstab, a hell of a DBI idiom" (http://www.perlmonks.org/index.pl?node_id=313934).