在Snowflake中PIVOT的聚合函数中使用concatate列

Question

SELECT *
from (SELECT Ant, Bird, Cat, Dog, Egg, Fish, Gold, Hen, RANK() OVER 
(PARTITION BY (Ant|| Bird|| Cat|| Dog|| Egg||Fish) ORDER BY Dog) AS ROW_COUNT
FROM TABLE1 WHERE Gold = '01')

pivot( MAX(Egg||Fish||Hen) for ROW_COUNT IN (1,  2,    3,    4,    5,    6,    7,    8,    9,    10)
) 
as QRY
;

基本上我想让这个工作在雪花，但它没有，因为雪花不允许我在聚合函数内连接列。然而，我注意到这段代码可以在Oracle DB中运行良好。有人能帮我一下吗?我试图在MAX()之前创建连接列，但从顶部返回不同的结果(在Oracle DB中测试)。例如,

SELECT *
from (SELECT Ant, Bird, Cat, Dog, Egg, Fish, Gold, Hen, Egg||Fish||Hen AS concat_col ,RANK() OVER 
(PARTITION BY (Ant|| Bird|| Cat|| Dog|| Egg||Fish) ORDER BY Dog) AS ROW_COUNT
FROM TABLE1 WHERE Gold = '01')

pivot( MAX(concat_col) for ROW_COUNT IN (1,  2,    3,    4,    5,    6,    7,    8,    9,    10)
) 
as QRY
;

以上是我所期望的不同结果。

Answer 1

由于个人原因，我不能发布实码。无论如何，我已经找到了解决方案，只需从select中删除连接列。这阻止了枢轴的适当转置。

SELECT *
from (SELECT Ant, Bird, Cat, Dog, Gold, Egg||Fish||Hen AS concat_col ,RANK() OVER 
(PARTITION BY (Ant|| Bird|| Cat|| Dog|| Egg||Fish) ORDER BY Dog) AS ROW_COUNT
FROM TABLE1 WHERE Gold = '01')

pivot( MAX(concat_col) for ROW_COUNT IN (1,  2,    3,    4,    5,    6,    7,    8,    9,    10)
) 
as QRY
;