下面是bigquery标准sql
如果您预先知道产品名称（例如示例中的“1”、“2”、“3”），并且只有很少的名称，那么您可以使用下面的简单版本

# standardSQL

SELECT name,
  MAX(product = '1') AS has1,
  MAX(product = '2') AS has2,
  MAX(product = '3') AS has3
FROM `project.dataset.table`
GROUP BY name

如果要应用于您问题中的示例数据（我假设您的产品是字符串数据类型）

WITH `project.dataset.table` AS (
  SELECT 'Andy' name, '1'  product UNION ALL
  SELECT 'Bill', '2' UNION ALL
  SELECT 'Cole', '2' UNION ALL
  SELECT 'Andy', '2' UNION ALL
  SELECT 'Bill', '1' UNION ALL
  SELECT 'Cole', '2' UNION ALL
  SELECT 'Dave', '3' 
)

结果是

Row name    has1    has2    has3     
1   Andy    true    true    false    
2   Bill    true    true    false    
3   Cole    false   true    false    
4   Dave    false   false   true

如果事先不知道产品名称和/或产品的数量，只知道几个以下的版本就很方便了

EXECUTE IMMEDIATE '''
SELECT name,''' || (
  SELECT STRING_AGG(DISTINCT "MAX(product = '" || product || "') AS has" || product)
  FROM `project.dataset.table`
) || '''  
FROM `project.dataset.table`
GROUP BY name
'''

输出完全相同
正如您在这里看到的，整个查询是动态组装的，所以您不必担心产品的数量和名称
下面的版本与上面的相同，但更易于管理/阅读

EXECUTE IMMEDIATE FORMAT('''
SELECT name, %s 
FROM `project.dataset.table`
GROUP BY name
''', (
  SELECT STRING_AGG(DISTINCT "MAX(product = '" || product || "') AS has" || product)
  FROM `project.dataset.table`
))

您可以使用聚合和简单逻辑：

select name,
       countif(product = 1) > 0 as has_1,
       countif(product = 2) > 0 as has_2,
       countif(product = 3) > 0 as has_3
from t
group by name;

你也可以使用 logical_or() :

select name,
       logic_or(product = 1) as has_1,
       logic_or(product = 2) as has_2,
       logic_or(product = 3) as has_3
from t
group by name;