SequelPivot Introduction

SequelPivot is a perl script that creates SQL code that makes a table VIEW which displays the table data as a pivot table.

A pivot table is a table of data that contains not only the data, but subtotal and grand-total lines at appropriate places, or other aggregate function data. Rows which contain aggregate data, as opposed to simple data, will have a NULL value in the column(s) which are being summed over. The subtotals will be in new columns created by the view.

This script generates SQL code that works with PostgreSQL. It may work with other databases as well. Some databases may be capable of generating these kinds of reports without SequelPivot. SequelPivot was originally written as a SequelBasis component.

Example

Given a table named interest, the command:

sequelpivot.pl 'interest;interest_pivot_1;currency\bank;interest;sum(interest)'

creates a view of the interest table, named interest_pivot_1 , which creates this data. Suppose that you do not wish to see every interest entry in the table, but only the aggregates. Then use the command:

sequelpivot.pl 'interest;interest_pivot_2;currency\bank;;sum(interest)'

which creates a view which generates this data. Now, it doesn't make sense to add the values in different currencies directly, so let's get rid of the summation for the first column field 'currency', by using the 's' option for the first column field:

sequelpivot.pl 'interest;interest_pivot_3;currency\bank;;sum(interest);s'

generates this. Now, suppose that we want to group data by year, and by currency, ignoring which bank paid the interest, but desiring to know how many banks paid interest. The command

sequelpivot.pl 'interest;interest_pivot_4;year\currency;;sum(interest)\count(DISTINCT bank);\s'

Will do this. It again is necessary to have the 's' option in column 2, because it does not make sense to add dollars to swiss francs. There is one problem, however, with the resulting output: the last row of the table includes sums of all values, so it is also summing dollars and swiss francs. To avoid this, use the option list 's\s' instead of the option list '\s', so that sums for the first two columns do not get added together; or place the currency column to the left, and use the similar view:

sequelpivot.pl 'interest;interest_pivot_5;currency\year;;sum(interest)\count(DISTINCT bank);s'

which generates this.

SequelPivot Options

SequelPivot uses one long string as its command-line option. This string must consist of 6 semicolon-separated strings:

$inputname	table name	Name of the source table
$pivotname	pivot view name	Name of the view that will be created by SequelPivot
@leftcols	columns used for grouping	Names of the columns that will be used for grouping, and appear at the left of the pivot table. It is possible to use a function of a column, instead of a simple column name, if you name the new column with an AS clause. For example, to group data by year, use: extract(year from "Sale Date") AS year
@centercols	non-aggregate columns	Columns containing data to be shown as-is from source table
@centerfuncs	aggregate functions of columns	Functions which will be used to calculate aggregate data. Each function will receive its own new column. Example: sum(column-name)
@optionlist	list of options for each @leftcols	There is only one option at the moment, the 's' option, which skips the calculation of a particular column subtotal.

The last 4 strings must consist of backslash-separated strings. @optionlist is optional.

Download

SequelBasis is copyrighted (2002, 2003, 2004), David Hoover.
SequelPivot is copyrighted (2002, 2003, 2004), David Hoover.

These programs are free software; you can redistribute them and/or modify them under the terms of the GNU General Public License as published by the Free Software Foundation; either version 2 of the License, or (at your option) any later version.

These programs are distributed in the hope that they will be useful, but WITHOUT ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU General Public License for more details. You should have received a copy of the GNU General Public License along with these programs; if not, write to the Free Software Foundation, Inc., 59 Temple Place - Suite 330, Boston, MA 02111-1307, USA.
Click here to download SequelBasis and SequelPivot in tar format.