SQL Currency Recursive CTE? [closed]

I have the below table:

At this moment I have a currency conversion from any currency to SEK. I would like to make a query that returns a source and target currency.

the result should look something like this:

UPDATED thank you for already this version: But I would also need to know conversion rates between other currencies. In this example USD - EUR will not show up.

The end result should also include the conversion between other currencies than SEK:

Any assistance?

Solution 1:

You don't need a recursive CTE for that. Just use union, which will also avoid generating the duplicate for SEK to SEK. I assume here the table name is "exchange":

select Year, month, CurrencyCode as SourceCurrency, 'SEK' TargetCurrency, value 
from exchange
union
select year, month, 'SEK', CurrencyCode, 1 / value
from exchange;

fiddle

Solution 2:

Try this:

select a.Year, a.month, a.CurrencyCode as SourceCur, b.CurrencyCode as TargetCur, a.value/b.value
from table_name a inner join table_name b
on a.Year = b.Year and a.month = b.month and (a.CurrencyCode = 'SEK' or b.CurrencyCode = 'SEK')

Basically join the table with itself to find all the possible conversion rates.

Fiddle

If you want all the conversion rates:

select a.Year, a.month, a.CurrencyCode as SourceCur, b.CurrencyCode as TargetCur, a.value/b.value
from table_name a inner join table_name b
on a.Year = b.Year and a.month = b.month and (a.CurrencyCode <> b.CurrencyCode or (a.CurrencyCode = 'SEK' and b.CurrencyCode = 'SEK'))