如何在BigQuery Standard SQL中实现动态数据逆透视？

我们有如下宽表（示例）：

需要转换为以下长表格式：

由于实际表有300+列且列集经常新增，不能使用硬编码列名的方案。

可以通过BigQuery的动态SQL结合INFORMATION_SCHEMA和UNPIVOT函数实现，无需硬编码列名，自动适配列的新增。

具体实现代码 #

DECLARE column_list STRING;

-- 从元数据中获取除product外的所有列，拼接为UNPIVOT所需格式
SET column_list = (
  SELECT STRING_AGG(DISTINCT CONCAT('`', column_name, '`'), ', ')
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table'
    AND column_name != 'product'
);

-- 执行动态生成的UNPIVOT语句
EXECUTE IMMEDIATE FORMAT("""
  SELECT product, attribute, value
  FROM `your_project.your_dataset.your_table`
  UNPIVOT (
    value FOR attribute IN (%s)
  )
""", column_list);

代码说明 #

• 自动获取列名：通过查询INFORMATION_SCHEMA.COLUMNS获取目标表的所有列名，排除product列后，用STRING_AGG拼接成UNPIVOT需要的格式。
• 动态执行逻辑：用EXECUTE IMMEDIATE执行拼接好的SQL语句，FORMAT函数负责将列列表插入到查询模板中。
• 适配列变化：后续新增的非product列会被自动纳入转换范围，无需手动修改代码。

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