将布尔列转置为表头名称（Google Sheets至BigQuery SQL）

方法1：BYROW + LAMBDA + UNNEST 组合（推荐，扩展性好） #

逐行处理原始数据，根据布尔值生成对应工作室的行，再展开堆叠，最后过滤无效行：

=ARRAYFORMULA(
  QUERY(
    UNNEST(
      BYROW(A2:D, LAMBDA(row, 
        LET(
          event_date, INDEX(row, 1),
          event_id, INDEX(row, 2),
          studio_1_booked, INDEX(row, 3),
          studio_2_booked, INDEX(row, 4),
          FILTER(
            {event_date, event_id, "studio_1"; event_date, event_id, "studio_2"},
            {studio_1_booked; studio_2_booked}
          )
        )
      ))
    ),
    "SELECT * WHERE Col3 IS NOT NULL", 0
  )
)

• 替换A2:D为你的数据范围（从第二行开始，包含所有列）
• LET定义临时变量，提升公式可读性
• FILTER仅保留布尔值为TRUE的工作室行

方法2：FLATTEN + QUERY 组合（适合简单场景） #

拆分两个工作室列后合并有效行：

=ARRAYFORMULA(
  QUERY(
    {
      FLATTEN(IF(C2:C=TRUE, A2:A, "")),
      FLATTEN(IF(C2:C=TRUE, B2:B, "")),
      FLATTEN(IF(C2:C=TRUE, "studio_1", "")),
      FLATTEN(IF(D2:D=TRUE, A2:A, "")),
      FLATTEN(IF(D2:D=TRUE, B2:B, "")),
      FLATTEN(IF(D2:D=TRUE, "studio_2", ""))
    },
    "SELECT Col1, Col2, Col3 WHERE Col1 IS NOT NULL UNION ALL SELECT Col4, Col5, Col6 WHERE Col4 IS NOT NULL", 0
  )
)

• C2:C和D2:D对应studio_1和studio_2列
• UNION ALL合并两个工作室的有效数据

方法1：CROSS JOIN UNNEST（扩展性强，适合多工作室场景） #

构造包含工作室状态和名称的数组，展开后过滤有效行：

WITH original_data AS (
  SELECT 
    event_date,
    event_id,
    studio_1,
    studio_2
  FROM `your-project.your-dataset.your-table` -- 替换为你的表路径
)
SELECT
  event_date,
  event_id,
  studio_name AS studio_booked
FROM original_data
CROSS JOIN UNNEST([
  STRUCT(studio_1 AS is_booked, 'studio_1' AS studio_name),
  STRUCT(studio_2 AS is_booked, 'studio_2' AS studio_name)
])
WHERE is_booked = TRUE
ORDER BY event_date, event_id

方法2：UNION ALL（适合工作室数量少的场景） #

拆分两个工作室的查询结果后合并：

SELECT 
  event_date,
  event_id,
  'studio_1' AS studio_booked
FROM `your-project.your-dataset.your-table`
WHERE studio_1 = TRUE

UNION ALL

SELECT 
  event_date,
  event_id,
  'studio_2' AS studio_booked
FROM `your-project.your-dataset.your-table`
WHERE studio_2 = TRUE

ORDER BY event_date, event_id

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