如何在Snowflake中将动态列转换为行

需求示例 #

输入表（日期列动态变化） #

期望输出 #

当输入表的日期列数量增加（如新增04-01-2022列），输出需自动对应新增行，无需修改基础SQL逻辑。

实现方案 #

1. 静态列转行（列名固定场景） #

如果日期列是固定已知的，直接使用UNPIVOT语法即可：

SELECT 
    product,
    country,
    brand,
    DATE,
    VALUE
FROM your_table
UNPIVOT (
    VALUE FOR DATE IN ("01-01-2022", "02-01-2022", "03-01-2022")
) AS unpvt;

注意：日期列名包含特殊字符-，需要用双引号包裹。

2. 动态列转行（列名不固定场景） #

针对日期列动态变化的情况，需通过动态SQL自动识别目标列并生成转换逻辑，以下是两种常用实现方式：

方式一：存储过程自动执行

创建存储过程，自动查询日期列并执行UNPIVOT：

CREATE OR REPLACE PROCEDURE dynamic_unpivot()
RETURNS VARCHAR
LANGUAGE SQL
AS
$$
DECLARE
    cols_str VARCHAR;
    sql_stmt VARCHAR;
BEGIN
    -- 匹配DD-MM-YYYY格式的日期列，生成列列表字符串
    SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ')
    INTO cols_str
    FROM INFORMATION_SCHEMA.COLUMNS
    WHERE TABLE_SCHEMA = 'your_schema'
      AND TABLE_NAME = 'your_table'
      AND COLUMN_NAME REGEXP '\\d{2}-\\d{2}-\\d{4}';

    -- 构建动态转换SQL
    sql_stmt := '
        SELECT 
            product,
            country,
            brand,
            DATE,
            VALUE
        FROM your_table
        UNPIVOT (
            VALUE FOR DATE IN (' || cols_str || ')
        ) AS unpvt;
    ';

    -- 执行SQL
    EXECUTE IMMEDIATE sql_stmt;
    RETURN '动态列转行执行完成';
END;
$$;

-- 调用存储过程
CALL dynamic_unpivot();

方式二：手动生成并执行SQL

如果不需要存储过程，可分两步操作：

1. 查询获取日期列列表：

SELECT LISTAGG('"' || COLUMN_NAME || '"', ', ') AS unpivot_columns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'your_schema'
  AND TABLE_NAME = 'your_table'
  AND COLUMN_NAME REGEXP '\\d{2}-\\d{2}-\\d{4}';

2. 将查询结果粘贴到UNPIVOT语句中执行：

SELECT 
    product,
    country,
    brand,
    DATE,
    VALUE
FROM your_table
UNPIVOT (
    VALUE FOR DATE IN ("01-01-2022", "02-01-2022", "03-01-2022", "04-01-2022")
) AS unpvt;

关键注意事项 #

• 正则表达式\\d{2}-\\d{2}-\\d{4}用于匹配DD-MM-YYYY格式的列名，可根据实际列名格式调整。
• 替换your_schema和your_table为实际的模式名称与表名称。
• 执行动态SQL需具备INFORMATION_SCHEMA查询权限，存储过程方式还需EXECUTE IMMEDIATE权限。

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