How to split a comma-separated value into new rows

Question

I have a table:

Id                   email                           causes
-------------------------------------------------------------------
7q9QhvI74       jonsnow@endtest-mail.io         water,nature,food
XDgj8dIyt       aryastark@endtest-mail.io       food,nature
IBk7HQ4pJ       sansa@endtest-mail.io           water,food

I want that the causes separated by commas come into a new row with same their same Id and email this way in Google BigQuery:

Id                   email                           causes
-------------------------------------------------------------------
7q9QhvI74       jonsnow@endtest-mail.io               water
7q9QhvI74       jonsnow@endtest-mail.io              nature
7q9QhvI74       jonsnow@endtest-mail.io               food
XDgj8dIyt       aryastark@endtest-mail.io             food
XDgj8dIyt       aryastark@endtest-mail.io             nature
IBk7HQ4pJ       sansa@endtest-mail.io                 water
IBk7HQ4pJ       sansa@endtest-mail.io                 food

Thanks

I have removed the tag spam; only tag the RDBMS you are *really* using. Though this question has been asked so many times that there is most certainly a duplicate for the RDBMS you are using. A simple search of "Split comma separated value into rows {MyRDBMS}" will give you a wealth of answers. — Thom A, May 11 '22 at 09:49
Does this answer your question? SQL Server - [Turning a Comma Separated string into individual rows](https://stackoverflow.com/questions/5493510/turning-a-comma-separated-string-into-individual-rows) MySQL - [SQL split values to multiple rows](https://stackoverflow.com/questions/17942508/sql-split-values-to-multiple-rows) Bigquery - [Splitting a string column in BigQuery](https://stackoverflow.com/questions/19413901/splitting-a-string-column-in-bigquery) — Thom A, May 11 '22 at 09:51

score 2 · Answer 1 · answered May 11 '22 at 10:08

2

You can use the split function to achieve this in BigQuery.

With your sample data you can perform the following:

SELECT id, email, split_causes
from sample_data, unnest(split(causes)) as split_causes

which results in the following:

you can simplify this even more if you can have nested records by removing the unnest as follows:

SELECT id, email, split(causes, ",")
from sample_data

which would result in the following:

answered May 11 '22 at 10:08

Daniel Zagales

2,948
2
5
18

Thanks. It's working. If we have to add condition in query then we could add WHERE in the end of query like: ```SELECT id, email, split(causes, ",") from sample_data where email="jonsnow@endtest-mail.io"``` – Bilal Shahid May 11 '22 at 10:23
1

Yes that would work fine, the only caveat would be if you wanted to filter on the cause easily, I would go with the unnest approach as it makes interacting with the individual rows easier. If you found this helpful please also consider marking the answer correct. – Daniel Zagales May 11 '22 at 10:29
Is unnest also work well if we have a dataset of hundreds of thousands of rows? – Bilal Shahid May 11 '22 at 10:32
Yes that would be fine. I would still follow various best practice around limiting what you select to reduce the amount of processing necessary. – Daniel Zagales May 11 '22 at 10:38

How to split a comma-separated value into new rows

1 Answers1