如何无需列名列表实现宽表UNPIVOT？报错问题求解

我有一张非常宽的表，想要在不逐一列出数百列的情况下执行UNPIVOT操作。尝试了如下代码：

WITH column_names_table as (
  SELECT column_name
  FROM `my_project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE 
    table_name = 'my_table'
    AND NOT column_name = 'time_utc'
)

SELECT time_utc, measurement, identifier 
FROM `my_project.dataset.my_table`
    UNPIVOT(
        measurement FOR identifier IN 
        (SELECT column_name from column_names_table)
    )

但出现报错：Syntax error: Unexpected keyword SELECT。如果将SELECT语句替换为字面列名列表则可正常运行。请问：

1. 能否修改代码使其生效？
2. 若不行，有没有其他方法可以将除少数列外的所有列进行UNPIVOT？
3. 这种方式不可行的原因是什么？

输入数据示例 #

期望输出数据 #

问题解答 #

1. 能否修改原代码使其生效？

原代码无法直接修改生效，因为UNPIVOT语法要求IN子句必须是明确的列名字面量列表，不支持子查询动态生成列名。要实现动态列的UNPIVOT，必须使用动态SQL，通过拼接生成完整的UNPIVOT语句。

示例动态SQL代码（BigQuery环境）：

DECLARE unpivot_columns STRING;

-- 拼接需要UNPIVOT的列名
SET unpivot_columns = (
  SELECT STRING_AGG(column_name, ', ')
  FROM `my_project.dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'my_table' AND column_name != 'time_utc'
);

-- 构造并执行动态UNPIVOT语句
EXECUTE IMMEDIATE format("""
  SELECT time_utc, measurement, identifier
  FROM `my_project.dataset.my_table`
  UNPIVOT(
    measurement FOR identifier IN (%s)
  )
""", unpivot_columns);

2. 其他替代方法

除了动态SQL，还可以用JSON函数实现动态UNPIVOT，不需要提前列所有列：

SELECT 
  time_utc,
  CAST(json_value AS FLOAT64) AS measurement, -- 根据实际数据类型调整
  json_key AS identifier
FROM `my_project.dataset.my_table` t,
UNNEST(REGEXP_EXTRACT_ALL(TO_JSON_STRING(t), r'"([^"]+)":([^,}]+)')) AS kv_pair,
-- 拆分键值对
UNNEST([STRUCT(
  SPLIT(kv_pair, ':')[OFFSET(0)] AS json_key,
  SPLIT(kv_pair, ':')[OFFSET(1)] AS json_value
)])
WHERE json_key != 'time_utc'

这种方法通过将每行转为JSON字符串，拆分出所有键值对，再过滤掉不需要的列，适合列数多且不需要严格匹配UNPIVOT语法的场景。

3. 原方法不可行的原因

BigQuery的UNPIVOT属于静态语法元素，SQL解析器在编译阶段就需要确定IN子句中的列名，验证这些列是否存在于源表中。而子查询的结果是在运行阶段才会计算的，无法在解析阶段提供明确的列信息，因此会直接触发语法错误。

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