We have CurrencyRatestable as follows:
We need to arrange this table horizontally as:
TransactionCurrency|EUR rate|INR Rate|USD Rate|Effectie Date
GBP |0.893 |0.0115 |0.7992 |2019-06-18 00:00:00:000
INR |78.0005 |1 |69.7242 |2019-06-18 00:00:00:000
USD |1.1187 |0.0143 |1 |2019-06-18 00:00:00:000
EUR |1 |0.0128 |0.8939 |2019-06-18 00:00:00:000
GBP |0.8902 |0.0114 |0.7943 |2019-06-19 00:00:00:000
INR |78.0755 |1 |69.6667 |2019-06-19 00:00:00:000
USD |1.1207 |0.0144 |1 |2019-06-19 00:00:00:000
EUR |1 |0.0128 |0.8923 |2019-06-19 00:00:00:000
and so on
I have basic knowledge of SQL. Can PIVOT function do this? What should be the starting point of the query? As my trial gave error.
select * from (SELECT
[BaseCurrency] ,
[TransactionCurrency],
[EffectiveDate],
[Rate]
FROM [PDI].[dbo].[Dim_CurrencyRates]
)cur
PIVOT(
FOR [BaseCurrency] in ('EUR','INR','USD')
)As PivTab
I often use max
when I don't need to aggregate.
select
TransactionCurrency,
EUR as [EUR rate],
INR as [INR Rate],
USD as [USD Rate],
EffectiveDate
from [PDI].[dbo].[Dim_CurrencyRates]
pivot(
max(Rate)
for BaseCurrency in (EUR, INR, USD)) as pv