在BigQuery（dbt Cloud）中如何实现多列code与维度表的正确关联？

你当前的问题核心是未先完成列转行操作，直接用OR关联会导致code值无法正确映射到独立行，还会产生不符合预期的重复数据。正确的步骤是先把t1中2021_code、2022_code、2023_code这三列的非空值转为独立行，再和t2关联获取label。

以下是两种适配BigQuery的实现方式：

方法1：使用UNPIVOT语法（推荐，直观易维护） #

WITH unpivoted_t1 AS (
  SELECT 
    id,
    code
  FROM t1
  UNPIVOT (
    code FOR year_code IN (2021_code, 2022_code, 2023_code)
  )
  WHERE code IS NOT NULL -- 过滤空的code值
)
SELECT 
  ut.id,
  ut.code,
  t2.label
FROM unpivoted_t1 ut
JOIN t2 ON ut.code = t2.code
ORDER BY ut.id, ut.code;

方法2：使用UNNEST+数组构造 #

WITH unpivoted_t1 AS (
  SELECT
    id,
    UNNEST([2021_code, 2022_code, 2023_code]) AS code
  FROM t1
  WHERE UNNEST([2021_code, 2022_code, 2023_code]) IS NOT NULL
)
SELECT 
  ut.id,
  ut.code,
  t2.label
FROM unpivoted_t1 ut
JOIN t2 ON ut.code = t2.code
ORDER BY ut.id, ut.code;

说明 #

• UNPIVOT是BigQuery专门用于列转行的语法，清晰指定要转换的列集合，自动生成每行对应的code值，再过滤空值即可得到t1的扁平化数据。
• UNNEST方式是将三个code列打包成数组，再打散为多行，同时过滤空值，适合灵活处理动态列的场景。
• 两种方式处理后，再与t2通过code等值关联，就能得到你需要的最终结果表。

你之前的SQL错误在于：用OR关联时，无法将每个code值映射为独立行，且code列无法明确对应到t1的某一列，最终会出现数据重复或值不匹配的问题。

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