0

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

Bilal Shahid
  • 173
  • 1
  • 2
  • 8
  • Specify DBMS, including its version. – Akina May 11 '22 at 09:49
  • 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
  • 1
    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

1 Answers1

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:

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

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="[email protected]"``` – 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