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.