在BigQuery中没有很好的做法，但您可以在下面执行想法

第1步

查询

  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] 
）
 

因此，您会得到如下的字符串（它被格式化为了便于阅读）

  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] 
 

步骤2

只需运行以上组合查询 结果如下

  group d_date1 d_date2 
 group1 15 30 
 

注1

有许多团体可以进行太多的手动工作。

在这种情况下 - 第1步可以帮助您生成查询

注意2

以上步骤可轻松实现您选择的任何客户端，或者您可以在BigQiery Web UI

呦您可以在我的其他帖子中看到更多关于摆动的信息。

如何在BigQuery中缩放转轴？

请注意 - 每张表格有10K列的限制 - 如此您受到10K组织的限制。

您也可以在下面看到简化示例（如果上面的一个太复杂/冗长）：





在BigQuery中重复显示字段

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?

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

Above steps are easily implemented in any client of your choice or you can just run those in BigQiery 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