Max Maischein

I don't mind working with Spreadsheets. Much of my work consists of creating Spreadsheets from SQL queries. Sometimes, the resulting spreadsheet should be a pivot table, listing some values across the spreadsheet. For most of my Spreadsheet-generation needs, Querylet is sufficient, but it cannot create pivot tables.

To remedy the need to manually create pivot tables, I wrote DBIx::PivotQuery, which makes creating a pivot table from an SQL query very easy.

use DBIx::PivotQuery 'pivot_by';
my $rows = pivot_by(
    dbh       => $dbh,
    columns   => ['month'],
    rows      => ['region'],
    aggregate => ['sum(amount) as amount'],
    sql => <<'SQL');
  select
      month(date) as report_month
    , region
    , amount
  from mytable
SQL

The above code returns a data structure roughly like

[
  ['region','1','2',...,'11','12'],
  ['East',   0,  0 ,..., 10, 20 ],
  ['North',  0,  1 ,..., 10, 20 ],
  ['South',  0,  3 ,..., 10, 5  ],
  ['West',   0,  6 ,..., 8,  20 ],
]

The code was fairly easy to write once I realized that I should leave the aggregation to SQL instead of (re)implementing sum, count and all the other interesting aggregates in Perl. As the code simply wraps your SQL with another SQL statement, it cannot handle ORDER BY clauses well, but I've been somewhat OK with the order that the module chooses.

Speaking of the fun parts of Excel, one application idea I have is to write an Excel clone for the terminal with vi keybindings. Most of the constituent parts are already there, like Spreadsheet::Read and Tickit. But Tickit doesn't work under Windows and I haven't found the time to port it there.