Message-ID: <012201c25d89$1fc67e00$4201a8c0@beeblebrox>
From: "Michael Paesold" <mpaesold@gmx.at>
To: "shahbaz" <chaudhar@umich.edu>, <pgsql-sql@postgresql.org>
References: <1caf1b8c.0209141453.46561d80@posting.google.com>
Subject: Re: cross tab (pivtor table) sql
Date: Mon, 16 Sep 2002 15:58:23 +0200
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

shahbaz wrote:

> Hi all, I wonder if someone can help me write a sql query for the
> following scenario:
> I have a table that looks like the fillowing:
> [time] [stock] [price]
> 11111   MSFT    1
> 11112   ORCL    2
> 11112   SUNW    3
> 11112   SUNW    2
> 11113   ORCL    5
> 11114   MSFT    4
> 11115   ORCL    3
> etc.
>
> I need to convert the above table to the following:
> [time] [MSFT] [ORCL] [SUNW] ...
> 11111    1      3      1
> 11112    2      3      3 <-------|
> 11113    3      4      2 <-------|----these values are prices
> 11114    5      2      3 <-------|
> 11115    4      1      8
>
> (obviously, don't pay any attention to the actual numbers I used).

I am not sure if this is what you want, but I'll try. So to get
a row for each second with all stocks (that need to be know at the
time of writing the query), I would try this (not tested, could
be quite slow):

SELECT DISTINCT ON (time)
  time,
  (SELECT MAX(price) FROM stocks b WHERE b.time=a.time AND b.stock='PSQL')
AS PSQL,
  (SELECT MAX(price) FROM stocks b WHERE b.time=a.time AND b.stock='MSFT')
AS MSFT,
  (SELECT MAX(price) FROM stocks b WHERE b.time=a.time AND b.stock='ORCL')
AS ORCL
  FROM stocks a
  ORDER BY time;

Let me know if it works...

Regards, Michael Paesold