How to get the UNNEST in bigquery to unnest rows but keep some fields in one row only

Question

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.

Answer 1

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