Обсуждение: Crosstab SQL Question

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.

Thanks,

Matt

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

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.

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

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!