【BigQuery】カンマ区切り文字列を分割して縦持ちにする（SPLIT/UNNEST/CROSS JOIN）

やりたいこと

カラムcustomerに顧客名、カラムordersにカンマ区切りで注文商品名が格納されているテーブルを想定します。カンマ区切りの注文商品名を分割して縦持ちのデータに変換する方法を述べます。

結論

SPLITでカンマ区切り文字列を配列に変換します。
その後、UNNESTとCROSS JOINを組み合わせて配列の要素をテーブルの行に変換します。

WITH
  test_table AS (
  /*
    テスト用テーブルの作成
  */
    SELECT 'Aさん' AS customer, 'コーヒー,チョコレート' AS orders
    UNION ALL
    SELECT 'Bさん' AS customer, '紅茶,クッキー' AS orders
    UNION ALL
    SELECT 'Cさん' AS customer, 'コーヒー' AS orders
    UNION ALL
    SELECT 'Dさん' AS customer, 'コーヒー,チョコレート,クッキー' AS orders
    UNION ALL
    SELECT 'Eさん' AS customer, NULL AS orders
  )

SELECT
  customer
  ,split_order

FROM
  test_table

CROSS JOIN
  UNNEST(SPLIT(orders,',')) AS split_order

BigQuery公式：配列内の要素をテーブル内の行に変換する
BigQuery公式：SPLIT関数