Move part of Select Statement to adjacent Column

Transact-SQL
1

Newbie and anything will help.

Tabel1

Stock Price
msft 10
goog 20
fb 99
amzn 87
S&P 24
Dow 50

I want a final statement that will give me the results in this format.

Stock Price Index IndexPrice
msft 10 S&P 24
goog 20 Dow 50

MSFT will always be in the same row as S&P in row 1
GOOG will always be in the same row as Dow in row 2
I will only pull just these 4 data points and never anything else no mater how many data points are in this one table.

Is this even possible?

2

The proper way to do this would be to have another table where you list each stock and its index - which then would allow you the flexibility of using a different index for a given stock, if called for. Nonetheless, if you just want MSFT and GOOG indexed against S&P and DOW respectively, you can do the following:

SELECT
	a.stock,
	a.Price,
	b.stock AS [Index],
	b.Price AS [IndexPrice]
FROM
	Tabel1 AS a
	INNER JOIN
	( VALUES ('MSFT','S&P'), ('GOOG','DOW') ) si(stock,[Index])
		ON si.stock = a.stock
	INNER JOIN Tabel1 AS b
		ON b.Stock = si.[Index];