英文:
How to get the UNNEST in bigquery to unnest rows but keep some fields in one row only
问题
我有像这样的输入表格。
我想要获得如下所示的期望输出。
我尝试了下面的查询，但我得到了像这样的结果，但我只需要笔记和水果字段在一行上，就像上面期望的输出一样。
英文:
i have my inpt table like this
i want to get the expected output like this.
i tried below query like this and i got like this but notes and fru fields i need in only one row like above expected output
select parent,child,name,notes,fru
from plm-wave-dev.data_migration_sandbox.ref_des_not_empty,
UNNEST(GENERATE_ARRAY(1, qty))
please letme know is there any way to get the output like i am expecting.
答案1
得分: 0
以下是翻译好的部分：
使用数组生成器来创建正确数量的行的方法看起来不错。您可以使用生成的值系列上的case表达式来实现您想要的逻辑：
select parent, child, name,
case when n = 1 then notes end as notes,
case when n = 1 then fru end as fru
from plm-wave-dev.data_migration_sandbox.ref_des_not_empty
cross join unnest(generate_array(1, qty)) as n
英文:
The approach using the array generator to create the correct number of rows looks good. You can use case expressions on the generated series of values to implement the logic you want:
select parent, child, name,
case when n = 1 then notes end as notes,
case when n = 1 then fru end as fru
from plm-wave-dev.data_migration_sandbox.ref_des_not_empty
cross join unnest(generate_array(1, qty)) as n
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论