Re: Crosstab SQL Question

"Ross J. Reedstrom" wrote:
>
> On Thu, Jun 08, 2000 at 07:04:07PM -0500, Matthew wrote:
> > Is it possible to perform a crosstab query in postgres similar the
> > functionality that MS Access provides?
> >
> > I tried building the query in Access (against postgre 6.5.3 using ODBC)
> > and using the SQL created by Access, but it looks like very non-standard
> > SQL code and postgre doesn't support it.
>
> Well, gee, Matt, for those of us who are not regular users of Access,
> you might want to describe what a crosstab query is, and maybe even
> quote the non-standard SQL that access produces, so we can advise you
> on how to do the same thing with postgresql.

For a table such as:

CREATE TABLE uber_goober (
    salesrep    text,
    month        text,
    sales        numeric(14,2)
);

The MS Access SQL statement for a crosstab query might look like:

TRANSFORM Sum([sales]) AS [The Value]
SELECT uber_goober.salesrep
FROM uber_goober
GROUP BY uber_goober.salesrep
PIVOT uber_goober.month;

This would result in ouput where 'salesrep' values serve as row
headings, 'month' values serve as column headings, and 'sales' values
are summed (or some other aggregate function) for each corresponding
'salesrep'+'month'.

I.E.

salesrep    Apr    Feb    Jan    Mar
Bill        $101    $101    $100    $99
Larry        $98    $100    $101    $102
Scott        $70    $65    $75    $35

Of course you'd use date types and sort better etc., but that's besides
the point.

Crosstab queries provide an interesting view of data, but they can be
difficult to format into reports, or join with other tables or queries,
because, of course, you don't know what your column headings will be
ahead of time.  I find them most useful in and of themselves, without
doing anything fancier.

________________________
Ron Peterson
rpeterson@yellowbank.com