sql server - Populate a column via multi-dimensional lookup in SQL?

Question

I have the following two tables:

TABLE_A:

Date	USD	EUR
2020-01-31	1.11	0.89
2020-02-28	1.15	0.93

question from:https://stackoverflow.com/questions/65849288/populate-a-column-via-multi-dimensional-lookup-in-sql

Answer 1

The only join I can see that is common to both tables is the date value, you can join on that and then use a CASE expression to choose the appropriate value from tableA based on the value in tableB

SELECT CASE WHEN b.Currency = 'USD' THEN a.USD ELSE a.EUR END as [Amount]
FROM tableA AS a
INNER JOIN tableB AS b
ON b.Date = a.Date