Big Query Transpose

Question

I have a Google Big Query table with the following columns:

date  | user | group | value
----------------------------
date1 | user1 | group1 | 10
----------------------------
date1 | user2 | group1 | 5
----------------------------
date2 | user1 | group1 | 20
----------------------------
date2 | user2 | group1 | 10
---------------------------
etc...

Now I want to convert this to this:

group  | date1 | date2
----------------------
group1 | 15    | 30

So I want to have the sum of value for each day per group. I wrote a query that looks like this:

SELECT date, group, value FROM [table] GROUP BY date, group, value

But how do I transpose this so that each colums is a date and each row is a collection of totals for the value?

Answer 1

There is no nice way of doing this in BigQuery as of yet, but you can do it following below idea

Step 1

Run below query

SELECT 'SELECT [group], ' + 
   GROUP_CONCAT_UNQUOTED(
      'SUM(IF([date] = "' + [date] + '", value, NULL)) as [d_' + REPLACE([date], '/', '_') + ']'
   ) 
   + ' FROM YourTable GROUP BY [group] ORDER BY [group]'
FROM (
  SELECT [date] FROM YourTable GROUP BY [date] ORDER BY [date]
)

As a result - you will get string like below (it is formatted below for readability sake)

SELECT 
    [group], 
    SUM(IF([date] = "date1", value, NULL)) AS [d_date1],
    SUM(IF([date] = "date2", value, NULL)) AS [d_date2] 
FROM YourTable 
GROUP BY [group] 
ORDER BY [group]   

Step 2

Just run above composed query

Result will be like below

group   d_date1 d_date2  
group1  15      30      

Note 1: Step 1 is helpful if you have many groups to pivot so too much of manual work. In this case - Step 1 helps you to generate your query

Note 2: these steps are easily implemented in any client of your choice or you can just run those in BigQuery Web UI

You can see more about pivoting in my other posts.

How to scale Pivoting in BigQuery?
Please note – there is a limitation of 10K columns per table - so you are limited with 10K organizations.
You can also see below as simplified examples (if above one is too complex/verbose):
How to transpose rows to columns with large amount of the data in BigQuery/SQL?
How to create dummy variable columns for thousands of categories in Google BigQuery?
Pivot Repeated fields in BigQuery