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.