在Snowflake中展平长度与元素不同的数组列

针对数组长度和元素不固定的场景，需要通过动态SQL实现需求，步骤如下：

1. 提取所有唯一的水果元素
   先从数组列中拆解出所有不重复的水果名称，作为后续的列名：

SELECT LISTAGG(DISTINCT f.value, ',') AS all_fruits
FROM your_table, LATERAL FLATTEN(input => fruit_array) f;

2. 生成动态透视SQL
   利用上述结果构造动态SQL，通过PIVOT将每个水果转为单独列，判断每行是否包含该水果：

-- 生成PIVOT需要的IN子句内容
SET all_fruit_columns = (
    SELECT LISTAGG(DISTINCT '''' || f.value || '''', ', ')
    FROM your_table, LATERAL FLATTEN(input => fruit_array) f
);

-- 构造完整的透视查询语句
SET pivot_query = '
SELECT *
FROM (
    SELECT 
        t.*,
        f.value AS fruit,
        ''True'' AS exists_flag
    FROM your_table t,
         LATERAL FLATTEN(input => t.fruit_array) f
)
PIVOT (
    MAX(exists_flag) FOR fruit IN (' || $all_fruit_columns || ')
) AS pivot_result;
';

-- 执行动态SQL
EXECUTE IMMEDIATE $pivot_query;

说明： #

• 替换your_table为你的实际表名
• 执行后，数组中存在的水果列会显示True，不存在的则显示空值（匹配你需要的输出格式）
• 如果后续数组元素有新增，重新运行这段代码即可自动更新列

内容的提问来源于stack exchange，提问作者rumble_shark