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.
>
Yes this is a good simple example of a cross-tab query.  Is there any
way to provide this type of functionality from postgresql?  Perhaps a
custom function written by somebody.