混淆UNPIVOT

Question

我有一张显示货币汇率的表格。

​

​

我想将它撤消到下面的布局

​

​

但当我用

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;

我在其中一个货币代码上出错了

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

即使我试着

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

我在其中一个货币代码上出错了：

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

我做错了什么？

Answer 1

您需要将货币括在括号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;

Answer 2

这就是解决办法：

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) )

谢谢大家的帮助！