NAME

DBIx::PivotQuery - create pivot tables from queries

SYNOPSIS

  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 ],
  # ]

FUNCTIONS

# This should maybe return a duck-type statement handle so that people # can fetch row-by-row to their hearts content # row-by-row still means we need to know all values for the column key :-/

pivot_by

    my $l = pivot_by(
        dbh     => $test_dbh,
        rows    => ['region'],
        columns => ['date'],
        aggregate => ['sum(amount) as amount'],
        placeholder_values => [],
        subtotals => 1,
        sql => <<'SQL',
      select
          region
        , "date"
        , amount
        , customer
      from mytable
    SQL
    );

Transforms the SQL given and returns an AoA pivot table according to rows, columns and aggregate.

The last word (<c>\w+</c>) of each element of aggregate will be used as the aggregate column name unless aggregate_columns is given.

Supplying undef for a column name in rows will create an empty cell in that place. This is convenient when creating subtotals.

Options

headers

  headers => 1,

Whether to include the headers as the first row

Subtotals are calculated by repeatedly running the query. For optimization, you could first select the relevant (aggregated) rows into a temporary table and then create the subtotals from that temporary table if query performance is an issue:

  select foo, sum(bar) as bar, baz
    into #tmp_query
    from mytable
   where year = ?

   select foo, bar, baz from #tmp_query

pivot_list

  my $l = pivot_list(
      list      => @AoH,
      columns   => ['date'],
      rows      => ['region'],
      aggregate => ['amount'],
  );

The rows of @$l are then plain arrays not hashes. The first row of @$l will contain the column titles.

The column titles are built from joining the pivot column values by $; .

headers

  headers => 1,

Whether to include the headers as the first row

pivot_sql

  pivot_sql(
      columns => ['date'],
      rows    => ['region'],
      aggregate => ['sum(amount) as amount'],
      sql => <<'SQL' );
    select
        "date"
      , region
      , amount
    from mytable
  SQL

Creates SQL around a subselect that aggregates the given columns.

The SQL created by the call above would be

    select "region"
         , "date"
         , sum(amount) as amount
    from (
        select
            "date"
          , region
          , amount
        from mytable
    ) foo
    group by "region, "date"
    order by "region", "date"

Note that the values in the columns and rows options will be automatically enclosed in double quotes.

This function is convenient if you want to ccreate ad-hoc pivot queries instead of setting up the appropriate views in the database.

If you want to produce subtotals, this function can be called with the elements removed successively from $options{rows} or $options{columns} for computing row or column totals.

Unsupported features

Currently only one aggregate value is allowed.

Row aggregates ("totals") are not supported yet. Row aggregates will mean heavy rewriting of the SQL to wrap the aggregate function over the column names of the query.

SEE ALSO

DBI

REPOSITORY

The public repository of this module is http://github.com/Corion/DBIx-PivotQuery.

SUPPORT

The public support forum of this module is https://perlmonks.org/.

BUG TRACKER

Please report bugs in this module via the RT CPAN bug queue at https://rt.cpan.org/Public/Dist/Display.html?Name=DBIx-PivotQuery or via mail to dbix-pivotquery-Bugs@rt.cpan.org.

AUTHOR

Max Maischein corion@cpan.org

COPYRIGHT (c)

Copyright 2017 by Max Maischein corion@cpan.org.

LICENSE

This module is released under the same terms as Perl itself.

cpanm DBIx::PivotQuery

perl -MCPAN -e shell
install DBIx::PivotQuery