DBIx::PivotQuery - create pivot tables from queries


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

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


\# 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',
        , "date"
        , amount
        , customer
      from mytable

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`

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

Creates SQL around a subselect that aggregates the given

The SQL created by the call above would be

    select "region"
         , "date"
         , sum(amount) as amount
    from (
          , 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.




The public repository of this module is


The public support forum of this module is


Please report bugs in this module via the RT CPAN bug queue at
or via mail to [dbix-pivotquery-Bugs@rt.cpan.org](https://metacpan.org/pod/dbix-pivotquery-Bugs@rt.cpan.org).


Max Maischein `corion@cpan.org`


Copyright 2017 by Max Maischein `corion@cpan.org`.


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