CODEWITHSUNDEEP
sqlcsvgoogle-bigquery
John
John

Reputation: 323

How to split a comma-separated value into new rows

I have a table:

Id                   email                           causes
-------------------------------------------------------------------
7q9QhvI74       [email protected]         water,nature,food
XDgj8dIyt       [email protected]       food,nature
IBk7HQ4pJ       [email protected]           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       [email protected]               water
7q9QhvI74       [email protected]              nature
7q9QhvI74       [email protected]               food
XDgj8dIyt       [email protected]             food
XDgj8dIyt       [email protected]             nature
IBk7HQ4pJ       [email protected]                 water
IBk7HQ4pJ       [email protected]                 food

Thanks

Upvotes: 0

Views: 8166

Answers (1)

Daniel Zagales
Daniel Zagales

Reputation: 3034

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:

enter image description here

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:

enter image description here

Upvotes: 3

Related Questions