How can I extract comma delimited values from one column and put them each in separate column in Google Data Studio?

Question

Update: 1,2,3 are just examples it can also be 4,24,53

I have the following setup:

I store Data in BigQuery and use BigQuery as data source for my Data Studio project.
I have a column called Alarms and the data inside that column is as follow: it can be empty or 1 or 1,2 or 1,2,3 or 5,43,60 and so on. If it's empty or has 1 value then there is nothing to worry about, but if there are 2 or more values I have to do something.

name	Alarm
Mark
John	1
Eddie	1,2
Peter	1,2,3

What I need is to be able to put every value in a separate column or create a dropdown or something.
For example something like the table below or two drop down menus one to select the name and the other shows the alarms. (I prefer the drop downs).

name	Alarm
Mark
John	1
Eddie	1	2
Peter	1	2	3

Here I select Peter and the alarm drop down shows 3 alarms. or for Eddie it just shows 2 alarms and so on.

I read something about regex but I don't really understand how to put it to the test.
I found this online: (.+?)(?:,|$) but I don't know how to capture the output.

Answer 1

What I need is to be able to put every value in a separate column

Consider below approach

select * from (
  select * except(alarm)
  from your_table, 
  unnest(split(alarm)) flag with offset 
)
pivot (min(flag) as alarm for offset in (0,1,2,3,4))          

If applied to sample data in your question -output is