Difficult question

Hello,
I have a table with stock values, they are grouped by a stock id. now I want
to get the trend of each stock. I only need this for one value per w for
one month.
for example:
stockid price
1 20
2 10
3 5
1 21
2 9
3 5
1 24
2 8
3 4
1 28
2 5
3 5
now I wan tot group them like:
stockid price1 price2 price3 price4
1 20 21 24 28
2 10 9 8 5
3 5 5 4 5
This way I can tell what the trend of the prices are, going up or down.
Or is there an other way of doing this?
Any help is appreciated,
-MarkYour design is wrong; we need a date or something by which to arange
the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
Look at the rule about using scalar values in columns of tables to
model all relationships.
SELECT ticker_sym,
CASE WHEN quote_date = '2005-11-22'
THEN price END AS price_1,
CASE WHEN quote_date = '2005-11-23'
THEN price END AS price_2,
CASE WHEN quote_date = '2005-11-24'
THEN price END AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks,
Well actually there is a date column and other columns as well, I just used
these because I thought they where the importante ones, my mistake.
Table Def:
ID (PK)
StockID (FK)
Date (datetime)
ClosePrice (money)
This gives me the following result:
StockID, P1, P2, P3, P4
1, 20, NULL, NULL, NULL
1, NULL, 21, NULL, NULL
1, NULL, NULL, 24, NULL
1, NULL, NULL, NULL, 28
What I would like is
StockID, P1, P2, P3, P4
1, 20, 21, 24, 28
-Mark
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132859937.260224.130920@.f14g2000cwb.googlegroups.com...
> Your design is wrong; we need a date or something by which to arange
> the prices. Get a book on basic RDBMS and read Dr. Codd's 12 rules.
> Look at the rule about using scalar values in columns of tables to
> model all relationships.
> SELECT ticker_sym,
> CASE WHEN quote_date = '2005-11-22'
> THEN price END AS price_1,
> CASE WHEN quote_date = '2005-11-23'
> THEN price END AS price_2,
> CASE WHEN quote_date = '2005-11-24'
> THEN price END AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>|||SELECT ticker_sym,
SUM( CASE WHEN quote_date = '2005-11-22'
THEN price END) AS price_1,
SUM( CASE WHEN quote_date = '2005-11-23'
THEN price END) AS price_2,
SUM( CASE WHEN quote_date = '2005-11-24'
THEN price END) AS price_3
FROM StockHistory
GROUP BY ticker_sym;|||Thanks Celko,
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132861841.665270.278530@.g14g2000cwa.googlegroups.com...
> SELECT ticker_sym,
> SUM( CASE WHEN quote_date = '2005-11-22'
> THEN price END) AS price_1,
> SUM( CASE WHEN quote_date = '2005-11-23'
> THEN price END) AS price_2,
> SUM( CASE WHEN quote_date = '2005-11-24'
> THEN price END) AS price_3
> FROM StockHistory
> GROUP BY ticker_sym;
>