英文:
Bigquery: To convert rows(on groupby) to columns in bigquery w.r.t. row value
问题
需要将上面的表格转换/转置为BigQuery中的下面格式。
英文:
Have got a table data like below (sample data provided for single store, this data repeats for multiple store)
Need to convert/transpose above table to below format in bigquery
答案1
得分: 1
根据您的需求，您可以考虑使用PIVOT运算符。
例如：
select * from (
select * except(category)
from `projectId.dataset.table`
)
pivot (sum(sales) as sales, sum(item_sold) as item_sold for (type) in ('Loose', 'PP'))
输出：
但是，根据您的要求，使用PIVOT运算符不可能返回一个名为sales PP fruit的列。当使用单个PIVOT列时，我们只能包括一个for条件。
要进行多列的数据透视，您需要使用多个Pivot。您可以参考这个堆栈链接。
英文:
For your requirement you can consider using PIVOT operator.
For example:
select * from (
select * except(category)
from `projectId.dataset.table`
)
pivot (sum(sales) as sales,sum(item_sold) as item_sold for (type) in ('Loose','PP'))
Output:
But using pivot, as per your requirement it's not possible to return a column as sales PP fruit. When using a single PIVOT column we can only include one for condition.
To pivot multiple column you need to use Muliple Pivot's.You can refer to this stack link.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论