0

I am trying to pull price data for multiple stocks. Everything is in columns, including the tickers to when I try to pull data for more than one stock I can't figure out how to have the ticker in the column header for each stock. The below code works for one stock but returns nothing for two.

This is what I get for one stock:

RecDate     TKR PRI

9/18/2002   CVS 13.86

But I want to be able to get this:

RecDate     CVS    ORCL

9/18/2002   13.86   60

Have been using this to pull the prices:

SELECT RecDate, TKR, PRI FROM dbo.Stockdatabase 
WHERE RecDate >= DateAdd(YEAR, -5, GetDate()) AND TKR='CSCO' 
ORDER BY TKR

How do I make this work for multiple stocks? Thank you!

S3S
  • 24,809
  • 5
  • 26
  • 45
W Rogers
  • 3
  • 1

1 Answers1

2

You can use conditional aggregation or pivot:

SELECT RecDate,
       MAX(CASE WHEN TKR = 'CSCO' THEN PRI END) as pri_csco,
       MAX(CASE WHEN TKR = 'ABC' THEN PRI END) as pri_abc
FROM dbo.Stockdatabase s
WHERE RecDate >= DateAdd(YEAR, -5, GetDate()) AND
      TKR IN ('CSCO' , 'ABC')
GROPU BY RecDate
ORDER BY RecDate;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786