Crosstab SQL Question

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.

Ross
--
Ross J. Reedstrom, Ph.D., <reedstrm@rice.edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005

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.

...[gratuitous sarcasm snipped]...
you might want to describe what a crosstab query is...

A crosstab is also known in MS Excel as a "pivot table", if that helps
any. It facilitates drag-and-drop data analysis by creating arbitrary
2-D matrices aggregated from data columns. Very cool, and hard to
adequately describe, as it makes some complex operations very simple, and
I don't understand how it works underneath. IMO, it is one of the most
powerful data analysis tools in existence. It would be neat to be able
to do something similar in pgsql...

Regards,
Ed Loehr

"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

"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.

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.

...[gratuitous sarcasm snipped]...
you might want to describe what a crosstab query is...

A crosstab is also known in MS Excel as a "pivot table", if that helps
any. It facilitates drag-and-drop data analysis by creating arbitrary
2-D matrices aggregated from data columns. Very cool, and hard to
adequately describe, as it makes some complex operations very simple, and
I don't understand how it works underneath. IMO, it is one of the most
powerful data analysis tools in existence. It would be neat to be able
to do something similar in pgsql...

Applix has something called TM1 that does this. They have a (ug)
flash demo and some more documentation at

http://www.applix.com/itm1

It used to be available for Linux, but with the big reorg, who
knows.

-- cary

The crosstab functionality in MS Access is (I believe) a seamless
integration of Access and Excel, where pivot tables are actually
implemented.

As others have indicated, crosstabs are available in other data analysis
systems.
I have found a nice implementation of crosstabs in R, which is a
system/language for statistical analysis, data display, and data
plotting. <www.r-project.org>

R is a version of S, which is also a statistical analysis language.
There are lots of issues with how one displays a crosstab which are not
easily addressed within the context of RDBMS. I suggest the PostgreSQL
team not plan to add crosstabs to PostgreSQL, but simply refer enquiries
about crosstabs to the R team. The link between the two is not difficult
to establish.

On Thu, Jun 15, 2000 at 08:16:58AM -0400, Cary O'Brien wrote:

Is it possible to perform a crosstab query in postgres similar the
functionality that MS Access provides?

...
you might want to describe what a crosstab query is...

A crosstab is also known in MS Excel as a "pivot table", if that helps
any. It facilitates drag-and-drop data analysis by creating arbitrary
2-D matrices aggregated from data columns. Very cool, and hard to
adequately describe, as it makes some complex operations very simple, and
I don't understand how it works underneath. IMO, it is one of the most
powerful data analysis tools in existence. It would be neat to be able
to do something similar in pgsql...

If I understand these reports correctly a crosstab is not a relational
query, but a small version of an OLAP engine. Usually this is not done by a
relational database server. For instance Oracle cannot do it either, you
need Oracle Express for this.

Applix has something called TM1 that does this. They have a (ug)

TM1 is an OLAP engine, albeit one that has problems with a larger amount of
data.

Michael
--
Michael Meskes
Michael@Fam-Meskes.De
Go SF 49ers! Go Rhein Fire!
Use Debian GNU/Linux! Use PostgreSQL!

Paul Condon <pecondon@quiknet.com> writes:

There are lots of issues with how one displays a crosstab which are not
easily addressed within the context of RDBMS. I suggest the PostgreSQL
team not plan to add crosstabs to PostgreSQL, ...

As near as I could see, the data that was being asked for was exactly
what you could get from a GROUP BY with multiple group columns:

SELECT salesman, month, SUM(netprice) FROM salesorders
GROUP BY salesman, month;

However the raw SQL output would look like

Jim Jan 1100
Jim Feb 1500
...
Joe Jan 120
Joe Feb 1200
...

so what's really wanted is a frontend to reformat this into a nicely
presented table. I agree with Paul that that's not the task SQL is
designed for, whereas there are lots of data-visualization tools that
*are* designed for it.

I have found a nice implementation of crosstabs in R, which is a
system/language for statistical analysis, data display, and data
plotting. <www.r-project.org>

Yup, R is cool (and free). Anyone up for making an R-to-PGSQL
interface?

regards, tom lane