Date: Fri, 13 Sep 2002 16:18:54 -0700 (PDT)
Message-Id: <200209132318.QAA09972@newsguy.com>
To: pgsql-sql@postgresql.org
From: pacquet@newsguy.com
Subject: "Inverting" a table, OLAP-style

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?