BigQuery(標準SQL)、SQLにて列の要素の種類ごとの合計を求めたい

Question

下のようなtableが存在したときに、

id	name1	name2
1	TARO_1	HANAKO_0
1	TARO_2	REN_1
1	HANAKO_1	REN_2
1	HANAKO_0	TARO_3
1	TARO_2	REN_2
2	TARO_0	REN_2
2	HANAKO_0	TARO_1
3	HANAKO_0	REN_2
3	HANAKO_0	REN_1

下記のようにname1とname2の列の要素の種類ごとの合計を求めるBigQueryの標準SQLまたはSQLが分かりません。

id	HANAKO	TARO	REN
1	3	4	3
2	1	2	1
3	2	0	2

一応自分のコードのほうでは

With user1 AS
(
  SELECT
    id,
    name1,
  FROM
    table
)

, user2 AS
(
  SELECT
    id,
    name2,
  FROM
    table
)

,user AS
(
  SELECT
    id,
    name1 AS name,
  FROM
    user1
  UNION ALL
  SELECT
    id,
    name2 AS name,
  FROM
    user2
)

,hanako AS
(
  SELECT 
    DISTINCT id AS id,
    COUNT(*) AS count
  FROM
    user
  WHERE 
    name
  LIKE 
    'HANAKO_%'
  GROUP BY 
    1
)

select * from hanako

hanakoと同様にtaro,renについても同様に作り、それを最終的に結合したものを返す処理を考えていました。
しかし、上記の実装の場合、ユーザーが3人の場合はこれで済むかもしれませんが、大量に増えた場合は上記のコードでは厳しいと思います。

簡潔にかけてよい実装方法はありますでしょうか？
ご教授お待ちしております。

Answer 1

求めたい結果はおそらく以下のようなSQLで実現することができると思います。

WITH s (id, name) AS (
            SELECT id, name1 FROM t 
  UNION ALL SELECT id, name2 FROM t
)
SELECT
  id,
  SUM(CASE WHEN name LIKE 'HANAKO\_%' THEN 1 ELSE 0 END) AS HANAKO,
  SUM(CASE WHEN name LIKE 'TARO\_%'   THEN 1 ELSE 0 END) AS TARO,
  SUM(CASE WHEN name LIKE 'REN\_%'    THEN 1 ELSE 0 END) AS REN
FROM s
GROUP BY id

このSQLであれば、集計したいカラムが増えた場合も比較的簡単に修正できるかと思います。

ただ結果セットの数を動的に変更したい場合は動的SQLで実現する必要があります。