From: Michael Paesold <mpaesold@gmx.at>
To: shahbaz <chaudhar@umich.edu>
To: pgsql-sql@postgresql.org
Subject: Re: cross tab (pivtor table) sql
Date: Mon, 16 Sep 2002 15:58:23 +0200
Message-ID: <012201c25d89$1fc67e00$4201a8c0@beeblebrox> (raw)
References: <1caf1b8c.0209141453.46561d80@posting.google.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: mpaesold@gmx.at, chaudhar@umich.edu
Subject: Re: cross tab (pivtor table) sql
In-Reply-To: <012201c25d89$1fc67e00$4201a8c0@beeblebrox>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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