Thread: 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
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.
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!