SQL Change Column header to value from another column

Question

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!

Answer 1

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;