Accessのクロス集計表をBigQueryで実現する要件があり、どうやって実現するか困っていたところ、BigQueryにはPIVOT演算子があることが分かり、無事解決できたのでご紹介します。

PIVOT項目を静的に指定する方法と、動的に指定する方法の２通りについて記述しています。

縦持ちの「店別売上データ」から、"静的"に横持ちの「店別売上データ_PIVOT」に変換　

店CD、店名毎に、期をPIVOT項目として売上げを集計する。

PIVOT演算子の文法

FROM
[テーブル or サブクエリ]
PIVOT(
[集約関数] [, ...]
FOR [入力項目]
IN ([PIVOT項目] [, ...])
)

[テーブル or サブクエリ]：PIVOT前のテーブル名、または、サブクエリを記載
[集約関数]：SUMやCOUNT等の集約関数を記載
[入力項目]：列を作る際の元となる項目を記載
[PIVOT項目]：列として出力する項目を記載

SQL

実際にSQLを作成し実行してみます。
今回は総計も出力したいので、サブクエリを使用しました。

CREATE OR REPLACE TABLE `店別売上データ_PIVOT` AS
SELECT
*
FROM
(
SELECT
`店CD`,
`店名`,
SUM(`売上`)OVER(PARTITION BY `店CD`,`店名`) AS `総計`,
`期`,
`売上`
FROM `店別売上データ`
)

PIVOT(
SUM(`売上`) FOR `期` IN (
'Q1','Q2','Q3','Q4'
　)
)
ORDER BY `店CD`
;

実行結果

想定通りの結果が得られました。

縦持ちの「店別売上データ」から、"動的"に横持ちの「店別売上データ_PIVOT」に変換　

先ほどの例はPIVOT項目が期だったので、Q1からQ4と静的に指定を行えましたが、日付等の可変の項目をPIVOT項目とし、PIVOT変換する方法をご紹介します。
PIVOT項目を可変とするため、データから日付情報を取得し、「EXECUTE IMMEDIATE FORMAT」を利用して実現します。

SQL

実際にSQLを作成し実行してみます。

---配列定義
DECLARE daily_labels ARRAY<STRING>;

--日付を配列に設定
SET daily_labels = (
　SELECT ARRAY_AGG(i.`日付`)
　FROM (SELECT DISTINCT `日付` FROM `店別売上データ` ORDER BY 1) AS i
);

--配列をUNSET展開しながらIN句に設定する文字列を生成する
EXECUTE IMMEDIATE FORMAT("""
　CREATE OR REPLACE TABLE `店別売上データ_PIVOT` AS
　SELECT
　*
　FROM
　(
　SELECT
　`店CD`,
　`店名`,
　SUM(`売上`)OVER(PARTITION BY `店CD`,`店名`) AS `総計`,
　`日付`,
　`売上`
　FROM `店別売上データ`
　)
　PIVOT(
　SUM(`売上`) FOR `日付` IN %s) ORDER BY `店CD`;
""", (SELECT CONCAT("(",STRING_AGG(CONCAT("'",dailys,"'"),","),")") FROM UNNEST(daily_labels) AS dailys))

実行結果

想定通りの結果が得られました。

まとめ

今回の要件は、動的に横持ちのテーブルに変換する必要があったため、「PIVOT演算子」と「EXECUTE IMMEDIATE FORMAT」を使用して、無事解決することが出来ました。

また、「PIVOT演算子」と逆で、横持ちを縦持ちに変換する「UNPIVOT演算子」というのもあるらしいので、試してみたいと思います。