Python Snowflake Snowpark 中的动态枢轴或横向展平到列

可以通过生成列列表进行透视来实现:

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import any_value

def main(session: snowpark.Session): 
    df = session.table("vnt")
    df = df.join_table_function("flatten", df["SRC"]).drop(["SEQ", "PATH", "INDEX", "THIS"])
    cols = [c[0] for c in df.group_by("key").agg(any_value("key")).collect()]
    df = df.pivot("key", cols).min("value")
    return df

输出:

EDIT:个

为了删除旋转列周围的'个，您需要按照文档的说明显式地为列设置别名:

PIVOT个

If you prefer the column names without quotes,或如果您希望输出的列名与输入的列名不同，则为you can include the column names in the AS clause, as shown below:

SELECT EMPID AS EMP_ID, "'JAN'" AS JANUARY, "'FEB'" AS FEBRUARY,
    "'MAR'" AS MARCH, "'APR'" AS APRIL
FROM monthly_sales
PIVOT(sum(amount) FOR MONTH IN ('JAN', 'FEB', 'MAR', 'APR')) AS p
ORDER BY EMPID;

使用Snowpark for Python:

import snowflake.snowpark as snowpark
from snowflake.snowpark.functions import any_value, col

def main(session: snowpark.Session): 
    df = session.table("vnt")
    df = df.join_table_function("flatten", df["SRC"]).drop(["SEQ", "PATH", "INDEX", "THIS"])
    cols = [c[0] for c in df.group_by("key").agg(any_value("key")).collect()]
    cols_alias = [col("'" + c + "'").alias(c) for c in cols]
    df = df.pivot("key", cols).min("value").select(cols_alias)
    return df

输出: