A 'dynamic' PIVOT

Hi.
I have been trying to tackle this issue with stock prices that I get every day. It comes in:

Date           Ticker           ClosingPrice
1/2/2006     CSCO            35.45
1/3/2006     CSCO            36.00
1/4/2006     CSCO            36.50
1/2/2006     IBM               98.00
1/3/2006     IBM               98.50
1/4/2006     IBM               99.55
1/2/2006     AAPL             79.50
1/3/2006     AAPL             79.85
1/4/2006     AAPL             78.75

I get pricing on thousands of stock prices each day

I have a portfolio with n number of stocks in each day.

I need to create a matrix of data for a dynamic set of tickers each time to look like:

Date           CSCO             IBM             AAPL
1/2/2006     35.45             98.00          79.50
1/3/2006     36.00             98.50          79.85
1/4/2006     36.50             99.55          78.75

The hard part is that the universe of names can and will change each time.

 If there are any missing days then use previous days price.


Any help would be greatly appreciated.

Mark
meariskAsked:
Who is Participating?
 
gheistConnect With a Mentor Commented:
SQL databases prefer such reports you can later visualize with whatever you have:

Date           Symbol  Price
1/2/2006     CSCO       35.45
1/2/2006     IBM          98.00    
0
 
meariskAuthor Commented:
???

I don't understand
0
 
gheistCommented:
X                 Y               Z
Date           Symbol     Price
1/2/2006     CSCO       35.45
1/2/2006     IBM          98.00
1/2/2006     AAPL        79.50
1/3/2006     CSCO ....
...
...
0
Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
meariskAuthor Commented:


So are you saying that this can not be done?

Date           CSCO             IBM             AAPL
1/2/2006     35.45             98.00          79.50
1/3/2006     36.00             98.50          79.85
1/4/2006     36.50             99.55          78.75
0
 
m1tk4Connect With a Mentor Commented:
It can be done, however because you are dealing with an arbitrary (and changing) number of columns in the resulting dataset, you most likely will be better off implementing it in your presentation layer (where it really belongs, by the way).

This can be written in PL/PgSQL, however it will look more like a hack than something legible and/or maintainable in the future.
0
 
gheistCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.
I will leave the following recommendation for this question in the Cleanup topic area:

Split between gheist http:#16661838 and m1tk4 http:#16664267

Any objections should be posted here in the next 4 days. After that time, the question will be closed.

gheist
EE Cleanup Volunteer
0
All Courses

From novice to tech pro — start learning today.