0

I was trying to use pivot for x columns, I don't want to do it by typing manually. I try to used DECLARE category STRING; SET category but it can’t work on Scheduled queries in google BigQuery, Could anyone help me on that. For example, I have some data like below.

WITH data AS (
  SELECT DATE '2023-05-01' AS event_date, 'action_CN' AS category, 'A' AS user_id UNION ALL
  SELECT DATE '2023-05-01', 'action_EN', 'B' UNION ALL
  SELECT DATE '2023-05-02', 'action_JP', 'A' UNION ALL
  SELECT DATE '2023-05-02', 'action_KO', 'B' UNION ALL
  SELECT DATE '2023-05-02', 'action_OTHER', 'D' UNION ALL
  SELECT DATE '2023-05-02', 'adventure_CN', 'D' UNION ALL
  SELECT DATE '2023-05-02', 'action_CN', 'D' UNION ALL
  SELECT DATE '2023-05-03', 'action_EN', 'A' UNION ALL
  SELECT DATE '2023-05-03', 'action_JP', 'B' UNION ALL
  SELECT DATE '2023-05-03', 'action_EN', 'C' UNION ALL
  SELECT DATE '2023-05-03', 'action_JP', 'D' UNION ALL
  SELECT DATE '2023-05-03', 'action_EN', 'D' UNION ALL
  SELECT DATE '2023-05-03', 'action_JP', 'D' UNION ALL
  SELECT DATE '2023-05-04', 'action_EN', 'A' UNION ALL
  SELECT DATE '2023-05-04', 'action_JP', 'C' UNION ALL
  SELECT DATE '2023-05-05', 'action_EN', 'A' UNION ALL
  SELECT DATE '2023-05-05', 'action_JP', 'C' UNION ALL
  SELECT DATE '2023-05-06', 'action_EN', 'A' UNION ALL
  SELECT DATE '2023-05-06', 'action_JP', 'C'
)
SELECT * FROM data;

I want a pivot table by user_id, count(distinct event_date) column by category to look like this below.

user_id action_CN action_EN action_JP action_KO action_OTHER adventure_CN
A 1 4 1
B 1 1 1
C 1 3
D 1 1 2 1 1
Paul Maxwell
  • 33,002
  • 3
  • 32
  • 51
Jutarut Junchaiyapoom
  • 37
  • 1
  • 5
  • There are many solutions to pivoting tables available here: https://stackoverflow.com/questions/26272514/how-to-pivot-table-in-bigquery – Oleg Solovyev Jul 19 '23 at 14:37
  • SQL doesn't permit dynamically named columns, to achieve that you *need* `dynamic sql` to achieve this. https://cloud.google.com/bigquery/docs/reference/standard-sql/dynamic_sql – Paul Maxwell Jul 20 '23 at 08:02
  • see: https://towardsdatascience.com/pivot-and-scripting-in-bigquery-cb096ff0eca2 – Paul Maxwell Jul 20 '23 at 08:07

0 Answers0