pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feed"Inverting" a table, OLAP-style
2+ messages / 2 participants
[nested] [flat]
* "Inverting" a table, OLAP-style
@ 2002-09-13 23:18 pacquet@newsguy.com
2002-09-16 14:55 ` Re: "Inverting" a table, OLAP-style Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 2+ messages in thread
From: pacquet@newsguy.com @ 2002-09-13 23:18 UTC (permalink / raw)
To: pgsql-sql
I have a data-collection app, which creates a table like this, more or less:
t timestamp
name varchar
value numeric
the number of distinct values of <name> is 800 and growing, besides being
data-dependent, so it might be pretty inconvenient to have a single table with
800 columns. (and we might exceed the 1600-column limit someday)
But clients WOULD like to view the data sometimes as though it were:
t x y z
----------------------------------------------------------------
t1 value when name='x' value when name='y' value when name='z'
if they do a SELECT on time=t1 and (name='x' or name='y' or name='z')
This seems like a fairly common desire. I know we could use Pivot Tables in
Excel, or otherwise do it on the client, but the highest-leverage solution
would be to present this abstraction in the server. Any ideas on how to do
this?
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: "Inverting" a table, OLAP-style
2002-09-13 23:18 "Inverting" a table, OLAP-style pacquet@newsguy.com
@ 2002-09-16 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2002-09-16 14:55 UTC (permalink / raw)
To: pacquet@newsguy.com; +Cc: pgsql-sql
pacquet@newsguy.com writes:
> ... I know we could use Pivot Tables in
> Excel, or otherwise do it on the client, but the highest-leverage solution
> would be to present this abstraction in the server.
Joe Conway's contrib/tablefunc module (new for 7.3) has a "crosstab"
facility that I think might do what you want. Perhaps you'd like to
snag a 7.3 beta copy and try it out.
regards, tom lane
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-16 14:55 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-13 23:18 "Inverting" a table, OLAP-style pacquet@newsguy.com
2002-09-16 14:55 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox