sql-serverpivotcurrency-exchange-rates

Pivoting table in SQl Server


We have CurrencyRatestable as follows:

enter image description here

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

Solution

  • 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