-1

I have imported my BigQuery database into spreadsheet using the SyncWith add-on. Inside on of my columns I have a CSV I want to put those values into new rows.

This is what I have

model alarm
X 1,34,60,1000
Y 2,4,5,900,1000
Z 1

This is what I need

model alarm
X 1
X 34
X 60
X 1000
Y 2
Y 4
Y 5
Y 900
Y 1000
Z 1

I found the following example here, but unfortunately it doesn't work for me because "CROSS APPLY" is not recognized in BigQuery. Is there a BigQuery alternative to this code below.

 SELECT A.[id],  
     Split.a.value('.', 'VARCHAR(100)') AS String  
 FROM  (SELECT [id],  
         CAST ('<M>' + REPLACE([string], ',', '</M><M>') + '</M>' AS XML) AS String  
     FROM  TableA) AS A CROSS APPLY String.nodes ('/M') AS Split(a);
shab1r
  • 43
  • 14

1 Answers1

2

Try this one:

with mytable as (
    select "X" as model, "1,34,60,1000" as alarm union all 
    select "Y", "2,4,5,900,1000" union all 
    select "Z", "1"
)
select model, newalarm
from mytable, unnest(split(alarm)) as newalarm

enter image description here

Sergey Geron
  • 9,098
  • 2
  • 22
  • 29