在Snowflake中替代CROSS APPLY实现宽表UNPIVOT转长表

我们刚从Microsoft SQL Server切换至Snowflake，此前大量使用以下通过CROSS APPLY将宽表转为长表的脚本：

SELECT 
    CONGLOM_IDc,
    DESTINATION_IDc,
    COHORTc,
    COHORT_PASS_TYPEc,
    ACCESS_SEASON,
    PASS_TYPE,
    First_Cohortc
FROM #PassTypeByYear2 M
CROSS APPLY
    (
    VALUES
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'18/19', "18/19_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'19/20', "19/20_Pass", First_Cohort), 
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'20/21', "20/21_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'21/22', "21/22_Pass", First_Cohort),
        (CONGLOM_ID, DESTINATION_ID, COHORT, COHORT_PASS_TYPE,'22/23', "22/23_Pass", First_Cohort)
    ) c (CONGLOM_IDc, DESTINATION_IDc, COHORTc, COHORT_PASS_TYPEc, ACCESS_SEASON, PASS_TYPE, First_Cohortc)

希望在Snowflake中无需多次使用UNPIVOT实现该功能，尝试转换语法时遇到如下报错：

SQL Error [2014] [22000]: SQL compilation error:
Invalid expression [M.CONGLOM_ID] in VALUES clause

源表#PassTypeByYear2示例： #

期望输出表（TOP 10示例）： #

解决方案 #

方法1：使用LATERAL JOIN替代CROSS APPLY #

Snowflake支持LATERAL关键字，功能等价于SQL Server的CROSS APPLY，可允许子查询引用外部表的列。修改后的代码如下：

SELECT 
    M.CONGLOM_ID,
    M.DESTINATION_ID,
    M.COHORT,
    M.COHORT_PASS_TYPE,
    c.ACCESS_SEASON,
    c.PASS_TYPE,
    M.First_Cohort,
    M.Consistent_Pass_All_Years
FROM #PassTypeByYear2 M
LATERAL JOIN (
    VALUES
        ('18/19', M."18/19_Pass"),
        ('19/20', M."19/20_Pass"),
        ('20/21', M."20/21_Pass"),
        ('21/22', M."21/22_Pass"),
        ('22/23', M."22/23_Pass")
) c (ACCESS_SEASON, PASS_TYPE)
-- 可选：过滤NULL值，根据业务需求决定
WHERE c.PASS_TYPE IS NOT NULL;

说明：

• 用LATERAL JOIN替换CROSS APPLY，这是Snowflake实现关联子查询引用外部列的标准方式。
• VALUES子句仅定义季节和对应的Pass类型列，其他维度列直接从外部表M中选取，简化代码避免重复书写。
• 若需过滤Pass类型为NULL的行，添加WHERE c.PASS_TYPE IS NOT NULL即可，比如示例中DESTINATION_ID=227的行，仅保留19/20的有效记录。

方法2：使用UNPIVOT（单次调用） #

如果不想用LATERAL，也可通过一次UNPIVOT实现，代码如下：

SELECT 
    CONGLOM_ID,
    DESTINATION_ID,
    COHORT,
    COHORT_PASS_TYPE,
    REPLACE(ACCESS_SEASON_COL, '_Pass', '') AS ACCESS_SEASON,
    PASS_TYPE,
    First_Cohort,
    Consistent_Pass_All_Years
FROM #PassTypeByYear2
UNPIVOT (
    PASS_TYPE FOR ACCESS_SEASON_COL IN (
        "18/19_Pass", "19/20_Pass", "20/21_Pass", "21/22_Pass", "22/23_Pass"
    )
)
-- 可选：过滤NULL
WHERE PASS_TYPE IS NOT NULL;

说明：

• UNPIVOT将指定列转换为行，ACCESS_SEASON_COL为临时列名，存储原列名（如"18/19_Pass"），通过REPLACE函数去掉后缀得到季节名称。
• 逻辑清晰，适合批量处理多列转换，同样支持过滤NULL值。

内容的提问来源于stack exchange，提问作者Eli Rush Kallison