pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedcross tab (pivtor table) sql
2+ messages / 2 participants
[nested] [flat]
* cross tab (pivtor table) sql
@ 2002-09-14 22:53 shahbaz <chaudhar@umich.edu>
0 siblings, 1 reply; 2+ messages in thread
From: chaudhar@umich.edu @ 2002-09-14 22:53 UTC (permalink / raw)
To: pgsql-sql
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).
Essentially I want to pivot my table (like in Excel or OLAP tools).
I can't use excel because I have too much data...I'd like to take care
of this in a database any way.
I tried the following using postgresql:
select time,
CASE WHEN stock='A' THEN max(price) ELSE sum(0) END AS A,
CASE WHEN stock='AA' THEN max(price) ELSE sum(0) END AS AA,
CASE WHEN stock='AACB' THEN max(price) ELSE sum(0) END AS AACB,
CASE WHEN stock='AAGI' THEN max(price) ELSE sum(0) END AS AAGI
...
from mytable
group by time, stock
(notice I had to use max(price) because there me be more than one
price update during a second).
Unfortunately this doesn't work, it just returns a bunch of zeroes.
It seems to me that there has to be a way of doing this without
resorting to expensive olap tools. Any ideas will be appreciated,
thanks.
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: cross tab (pivtor table) sql
@ 2002-09-16 13:58 Michael Paesold <mpaesold@gmx.at>
parent: shahbaz <chaudhar@umich.edu>
0 siblings, 0 replies; 2+ messages in thread
From: Michael Paesold @ 2002-09-16 13:58 UTC (permalink / raw)
To: shahbaz <chaudhar@umich.edu>; pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2002-09-16 13:58 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2002-09-14 22:53 cross tab (pivtor table) sql shahbaz <chaudhar@umich.edu>
2002-09-16 13:58 ` Michael Paesold <mpaesold@gmx.at>
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