RE: Crosstab SQL Question
От | Matthew |
---|---|
Тема | RE: Crosstab SQL Question |
Дата | |
Msg-id | 183FA749499ED311B6550000F87E206C0C9235@SRV обсуждение исходный текст |
Ответ на | Crosstab SQL Question (Matthew <matt@ctlno.com>) |
Список | pgsql-general |
> "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.
В списке pgsql-general по дате отправления: