Confusing UNPIVOT-CodePudding

I have a table that shows currency exchange rates thusly

I want to unpivot it to the following layout

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?

CodePudding user response：

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

CodePudding user response：

You need to enclose the currencies in brackets https://docs.snowflake.com/en/sql-reference/constructs/unpivot.html#examples

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;

CodePudding user response：

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!