如何在BigQuery中根据列值获取对应列名？

方法一：静态列检查（适合列数较少的场景） #

如果明确知道表的所有列名，可以手动逐个检查，通过UNION ALL收集符合条件的列名：

WITH check_columns AS (
  SELECT CASE WHEN col1 = 'Sam' THEN 'col1' END AS column_name FROM `your-project.your-dataset.your-table`
  UNION ALL
  SELECT CASE WHEN col2 = 'Sam' THEN 'col2' END FROM `your-project.your-dataset.your-table`
  UNION ALL
  SELECT CASE WHEN col3 = 'Sam' THEN 'col3' END FROM `your-project.your-dataset.your-table`
  -- 根据实际表结构，继续添加其他列的检查语句
)
SELECT DISTINCT column_name
FROM check_columns
WHERE column_name IS NOT NULL;

方法二：动态列检查（适合列数较多的场景） #

通过INFORMATION_SCHEMA自动获取表的所有列名，生成动态SQL遍历检查，无需手动列出每一列：

DECLARE column_list STRING;
DECLARE sql_query STRING;

-- 获取目标表的所有列名，拼接成检查语句片段
SET column_list = (
  SELECT STRING_AGG(
    FORMAT("CASE WHEN %s = 'Sam' THEN '%s' END", column_name, column_name),
    ' UNION ALL SELECT '
  )
  FROM `your-project.your-dataset.INFORMATION_SCHEMA.COLUMNS`
  WHERE table_name = 'your-table' -- 替换为你的表名
);

-- 构建完整的查询语句
SET sql_query = FORMAT("
  WITH check_columns AS (
    SELECT %s FROM `your-project.your-dataset.your-table`
  )
  SELECT DISTINCT column_name
  FROM check_columns
  WHERE column_name IS NOT NULL
", column_list);

-- 执行动态生成的SQL
EXECUTE IMMEDIATE sql_query;

注意：将代码中的your-project、your-dataset、your-table替换为你实际的项目ID、数据集名称和表名。

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