NAME

Data::Pivot - Perl module to pivot a table

SYNOPSIS

  use Data::Pivot;
  @newtable = pivot( table        => \@table,
                     headings     => \@headings,
                     pivot_column => $pivot_col_no,
                     layout       => 'vertical',
                     row_sum      => 'Sum',
                     row_titles   => 1,
                     format       => '%5.2f',
                   )

DESCRIPTION

With Data::Pivot you can pivot a table like this:

 Some    Fix   Columns      Pivot_Col     Num_Values

 aaa     bbb   ccc          01              12.20
 aaa     bbb   ccc          02             134.50
 aaa     bbb   ccc          03               1.25
 xxx     yyy   zzz          02              22.22
 xxx     yyy   zzz          03             111.11

Will be converted to:

 Some    Fix   Columns       01       02       03       Sum

 aaa     bbb   ccc          12.20   134.50     1.25   147.95
 yyy     xxx   zzz           0.00    22.22   111.11   133.33

The table can contain several columns of Num_Values, which will get into rows, if the layout is 'horizontal', like this:

 Some    Fix   Columns      Pivot_Col     Num_Val_1    Num_Val_2   Num_Val_3

 aaa     bbb   ccc          01              12.20         1.40         5.90
 aaa     bbb   ccc          02             134.50        12.00        12.30
 aaa     bbb   ccc          03               1.25        30.00       123.45
 xxx     yyy   zzz          02              22.22         7.80         8.88
 xxx     yyy   zzz          03             111.11       100.00        42.00

Will be converted to:

 Some    Fix   Columns                    01       02       03       Sum

 aaa     bbb     ccc     Num_Val_1       12.20   134.50     1.25   147.95
                         Num_Val_2        1.40    12.00    30.00    43.40
                         Num_Val_3        5.90    12.30   123.45   141.65
 xxx     yyy     zzz     Num_Val_1        0.00    22.22   111.11   133.33
                         Num_Val_2        0.00     7.80   100.00   107.80
                         Num_Val_3        0.00     8.88    42.00    50.88

Data::Pivot has only one function which does all the work.

Functions

pivot()

Parameters:

pivot receives several named parameters:

table => \@table

A reference to an array of arrays containing all the data but no headings.

In the last example above:

 @table = ( [ 'aaa', 'bbb', 'ccc', '01', 12.2, 1.4, 5.9 ],
            [ 'aaa', 'bbb', 'ccc', '02', 134.5, 12, 12.3 ],
            [ 'aaa', 'bbb', 'ccc', '03', 1.25, 30, 123.45 ],
            [ 'xxx', 'yyy', 'zzz', '02', 22.22, 7.8, 8.88 ],
            [ 'xxx', 'yyy', 'zzz', '03', 111.11, 100, 42 ]
          );

headings => \@headings

A reference to an array containing the column headings.

In the last example above:

 @headings = ('Some', 'Fix', 'Columns', 'Pivot_Col', 'Num_Val_1', 'Num_Val_2', 'Num_Val_3');

pivot_column => $no_of_col

The column number over which the pivoting takes place

In the last example above:

 $no_of_col = 3;

layout => 'horizontal'

'layout' determines whether the 'Num_Val' columns are arranged 'horizontal'ly or 'vertical'ly in the new table.

row_sum => 'Sum'

The title of the sum column, which sums up the new pivoted columns. If this is undef the column will be omitted.

row_title1 => 1

If this is true, a new column will be inserted after the fix columns if the layout is 'horizontal'. This column will have no heading and the contents will be the headings of the value columns.

format => '%5.2f'

Format may be a legal sprintf format string or a reference to a subroutine. The format string will be applied to each pivoted column and the sum column. The subroutine will be called with each pivoted column and the sum column as parameter.

The full function call for the above example is:

 @newtable = pivot( table => \@table,
                    headings     => \@headings,
                    pivot_column => $pivot_col_no,
                    row_sum      => 'Sum',
                    row_titles   => 1,
                    format       => '%5.2f',
                  );

AUTHOR

Bernd Dulfer <bdulfer@cpan.org>

With Patches from

Graham TerMarsch