Confusing UNPIVOT

C B 0
Viewed 37

I have a table that shows currency exchange rates thusly

enter image description here

I want to unpivot it to the following layout

enter image description here

but when I use

SELECT CREATION_DATE, CURRENCY, RATE
FROM (
 SELECT CREATION_DATE, JMD, ISK, COP, USD, EGP, PAB
 FROM EXCHANGE_RATES) XR
 
 UNPIVOT(RATE FOR CURRENCY IN 
         JMD, ISK, COP, USD, EGP, PAB
        ) AS U;

I get an error on one of the currency codes

SQL compilation error: syntax error line 7 at position 9 unexpected 'JMD'. syntax error line 8 at position 10 unexpected 'AS'.

Even if I try

 SELECT * FROM EXCHANGE_RATES
    UNPIVOT(CURRENCY FOR MO (JMD, ISK, COP, USD, EGP, PAB))

I get an error on one of the currency codes:

SQL compilation error: The type of column 'COP' conflicts with the type of other columns in the UNPIVOT list.

What am I doing wrong?

snowflake-cloud-data-platform sql
3 Answers

So get the error:

SQL compilation error: The type of column 'COP' conflicts with the type of other columns in the UNPIVOT list.

If I use the data like to looks how you have it:

aka:

with exchange_rates(creation_date, jmd, isk, cop, usd, egp, pab) as (
    select * from values
    ('2022-09-21 21:33:37.973'::timestamp_ntz, 153.3, 141.1, 4422.5, 1, 19.4, 1)
)
select * 
from exchange_rates
unpivot(rate for currency in (jmd, isk, cop, usd, egp, pab) )

reading the error message, and slowing down, it's complaining of types

with exchange_rates(creation_date, jmd, isk, cop, usd, egp, pab) as (
    select * from values
    ('2022-09-21 21:33:37.973'::timestamp_ntz, 153.3, 141.1, 4422.5, 1.0, 19.4, 1.0)
)
select *, system$typeof(jmd), system$typeof(cop), system$typeof(usd)
from exchange_rates
CREATION_DATE JMD ISK COP USD EGP PAB SYSTEM$TYPEOF(JMD) SYSTEM$TYPEOF(COP) SYSTEM$TYPEOF(USD)
2022-09-21 21:33:37.973 153.3 141.1 4,422.5 1 19.4 1 NUMBER(4,1)[SB2] NUMBER(5,1)[SB4] NUMBER(1,0)[SB1]

and the 1 will default to a different type to the 141.1, to test, forcing it all to ::number(20,2)

with exchange_rates(creation_date, jmd, isk, cop, usd, egp, pab) as (
    select * from values
    ('2022-09-21 21:33:37.973'::timestamp_ntz, 153.3::number(20,2), 141.1::number(20,2), 4422.5::number(20,2), 1.0::number(20,2), 19.4::number(20,2), 1.0::number(20,2))
)
select * 
from exchange_rates
unpivot(rate for currency in (jmd, isk, cop, usd, egp, pab) )

works:

CREATION_DATE CURRENCY RATE
2022-09-21 21:33:37.973 JMD 153.3
2022-09-21 21:33:37.973 ISK 141.1
2022-09-21 21:33:37.973 COP 4,422.5
2022-09-21 21:33:37.973 USD 1
2022-09-21 21:33:37.973 EGP 19.4
2022-09-21 21:33:37.973 PAB 1
Simeon Pilgrim0
Ian Kenney0

This is the solution:

WITH EXCHANGE_RATES(CREATION_DATE, JMD, ISK, COP) AS (
    SELECT  
  CREATION_DATE::TIMESTAMP_NTZ, JMD::NUMBER(19,6) AS JMD, ISK::NUMBER(19,6) AS ISK, COP::NUMBER(19,6) AS COP
  FROM  EXCHANGE_RATES
)
SELECT * 
FROM EXCHANGE_RATES
UNPIVOT(RATE FOR CURRENCY IN (JMD, ISK, COP) )

Thanks everyone for helping out!

C B0
Related