如何在Google BigQuery中动态逆透视（Unpivot）列序号2之后的所有列

针对列名不固定、仅需处理ordinal_position大于2的列的逆透视需求，有两种实用方法：

方法一：动态生成UNPIVOT语句 #

通过查询INFORMATION_SCHEMA获取目标列名，自动拼接UNPIVOT逻辑，适合需要标准UNPIVOT格式的场景：

1. 先获取需要逆透视的列名（替换成你的项目、数据集和表名）：

SELECT STRING_AGG(FORMAT('[%s]', column_name), ',' ORDER BY ordinal_position) AS unpivot_columns
FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
WHERE table_name = 'your_table' AND ordinal_position > 2

2. 用动态SQL全自动执行逆透视：

DECLARE unpivot_cols STRING;

SET unpivot_cols = (
  SELECT STRING_AGG(FORMAT('[%s]', column_name), ',' ORDER BY ordinal_position)
  FROM `your_project.your_dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your_table' AND ordinal_position > 2
);

EXECUTE IMMEDIATE FORMAT("""
  SELECT Province, Province_id, CarCategory, CarCategoryCount
  FROM `your_project.your_dataset.your_table`
  UNPIVOT (
    CarCategoryCount FOR CarCategory IN (%s)
  )
""", unpivot_cols);

方法二：用JSON函数实现无列名依赖的逆透视 #

无需提前获取列名，直接将行数据转为JSON后拆解键值对，适合列频繁变动的场景：

SELECT
  Province,
  Province_id,
  JSON_EXTRACT_SCALAR(kv, '$.key') AS CarCategory,
  CAST(JSON_EXTRACT_SCALAR(kv, '$.value') AS INT64) AS CarCategoryCount
FROM `your_project.your_dataset.your_table` t,
UNNEST(JSON_QUERY_ARRAY(TO_JSON_STRING(t), '$.f')) kv
WHERE JSON_EXTRACT_SCALAR(kv, '$.key') NOT IN ('Province', 'Province_id')

注意：如果数值列不是整数，要把INT64改成对应的数据类型（比如FLOAT64）。

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