BigQuery中通过键值对转列合并两张表的方法咨询

你原始SQL存在两个明显问题：

• 表别名配置错误：DataUsers才是存储name、lastName字段的用户表，你在FROM中给DataInfo设置别名i还读取i.name会直接报字段不存在错误
• 缺少聚合逻辑：未分组的情况下，每个key会对应一条独立记录，自然会出现多行列值为空的情况

修正后的可运行SQL如下：

SELECT
  u.userID,
  u.name,
  u.lastName,
  MAX(CASE WHEN i.key = 'address1' THEN i.value END) AS address1,
  MAX(CASE WHEN i.key = 'address2' THEN i.value END) AS address2,
  MAX(CASE WHEN i.key = 'address3' THEN i.value END) AS address3
FROM
  `DataUsers` u
INNER JOIN
  `DataInfo` i
ON
  u.userID = i.userID
WHERE
  i.key IN ('address1', 'address2', 'address3')
GROUP BY
  u.userID, u.name, u.lastName

逻辑说明：用MAX聚合函数会自动忽略CASE WHEN生成的NULL值，取到每个地址字段的真实值，再按用户唯一维度分组，最终每个用户仅返回一条完整记录。

BigQuery支持动态PIVOT语法，不需要手动罗列所有key值，写法如下：

EXECUTE IMMEDIATE format("""
SELECT * FROM (
  SELECT u.userID, u.name, u.lastName, i.key, i.value
  FROM `DataUsers` u
  INNER JOIN `DataInfo` i ON u.userID = i.userID
)
PIVOT(
  MAX(value) FOR key IN (%s)
)
""", (SELECT STRING_AGG(DISTINCT CONCAT("'", key, "'")) FROM `DataInfo`))

逻辑说明：先通过子查询查询DataInfo表中所有去重的key值，拼接到PIVOT语句的IN条件中，再通过EXECUTE IMMEDIATE执行动态生成的SQL，后续DataInfo新增任意key都会自动生成对应列，无需修改代码。

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