Thread: cross tab (pivtor table) 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.

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