From: chaudhar@umich.edu
To: pgsql-sql@postgresql.org
Subject: cross tab (pivtor table) sql
Date: 14 Sep 2002 15:53:24 -0700
Message-ID: <1caf1b8c.0209141453.46561d80@posting.google.com> (raw)
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.
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: chaudhar@umich.edu
Subject: Re: cross tab (pivtor table) sql
In-Reply-To: <1caf1b8c.0209141453.46561d80@posting.google.com>
* 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