Snowflake中PIVOT聚合函数内使用列拼接的兼容实现求助

Snowflake的PIVOT语法采用隐式分组规则：所有未出现在PIVOT子句的聚合函数、FOR关键字后的列，都会自动作为分组键。你调整后的代码在子查询中保留了Egg、Fish、Hen三个列，这三个列被纳入了分组依据，和Oracle端的分组逻辑（仅以Ant、Bird、Cat、Dog、Gold作为分组键）不一致，因此输出结果不同。

方案1：修正PIVOT写法 #

仅在子查询中保留分组所需列、拼接后的待聚合列、行号列即可，避免多余列触发错误分组：

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;

方案2：使用条件聚合实现（更稳妥） #

用显式分组的条件聚合写法完全规避不同数据库PIVOT实现差异的问题，逻辑透明可控，输出结果和Oracle端完全一致：

SELECT 
    Ant,
    Bird,
    Cat,
    Dog,
    Gold,
    MAX(CASE WHEN ROW_COUNT = 1 THEN concat_col END) AS "1",
    MAX(CASE WHEN ROW_COUNT = 2 THEN concat_col END) AS "2",
    MAX(CASE WHEN ROW_COUNT = 3 THEN concat_col END) AS "3",
    MAX(CASE WHEN ROW_COUNT = 4 THEN concat_col END) AS "4",
    MAX(CASE WHEN ROW_COUNT = 5 THEN concat_col END) AS "5",
    MAX(CASE WHEN ROW_COUNT = 6 THEN concat_col END) AS "6",
    MAX(CASE WHEN ROW_COUNT = 7 THEN concat_col END) AS "7",
    MAX(CASE WHEN ROW_COUNT = 8 THEN concat_col END) AS "8",
    MAX(CASE WHEN ROW_COUNT = 9 THEN concat_col END) AS "9",
    MAX(CASE WHEN ROW_COUNT = 10 THEN concat_col END) AS "10"
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'
)
GROUP BY Ant, Bird, Cat, Dog, Gold;

内容的提问来源于stack exchange，提问作者Elliot Ally Bright