24

Let's say I have a table in BigQuery containing 2 columns. The first column represents a name, and the second is a delimited list of values, of arbitrary length. Example:

Name | Scores
-----+-------
Bob  |10;20;20
Sue  |14;12;19;90
Joe  |30;15

I want to transform into columns where the first is the name, and the second is a single score value, like so:

Name,Score
Bob,10
Bob,20
Bob,20
Sue,14
Sue,12
Sue,19
Sue,90
Joe,30
Joe,15

Can this be done in BigQuery alone?

David M Smith
  • 2,212
  • 4
  • 21
  • 27

3 Answers3

19

Good news everyone! BigQuery can now SPLIT()!

Look at "find all two word phrases that appear in more than one row in a dataset".

There is no current way to split() a value in BigQuery to generate multiple rows from a string, but you could use a regular expression to look for the commas and find the first value. Then run a similar query to find the 2nd value, and so on. They can all be merged into only one query, using the pattern presented in the above example (UNION through commas).

Community
  • 1
  • 1
Felipe Hoffa
  • 54,922
  • 16
  • 151
  • 325
  • Nice - This is awesome! Are there any parameters to specify a range of elements to pull from the resulting array? – David M Smith Jun 11 '14 at 20:55
  • I'm not sure I understand. Post new question? See http://stackoverflow.com/a/24172995/132438 – Felipe Hoffa Jun 11 '14 at 21:51
  • Is it possible to just split the scores column into multiple columns namely scores1, scores2 ,scores3 etc? instead of creating a row for each value? – Nivi Apr 17 '18 at 14:49
18

Trying to rewrite Elad Ben Akoune's answer in Standart SQL, the query becomes like this;

WITH name_score AS (
SELECT Name, split(Scores,';') AS Score
FROM (
      (SELECT * FROM (SELECT 'Bob' AS Name ,'10;20;20' AS Scores)) 
      UNION ALL 
      (SELECT * FROM (SELECT 'Sue' AS Name ,'14;12;19;90' AS Scores))
      UNION ALL
      (SELECT * FROM (SELECT 'Joe' AS Name ,'30;15' AS Scores))
)) 
SELECT name, score
FROM name_score
CROSS JOIN UNNEST(name_score.score) AS score;

And this outputs;

+------+-------+
| name | score |
+------+-------+
| Bob  | 10    |
| Bob  | 20    |
| Bob  | 20    |
| Sue  | 14    |
| Sue  | 12    |
| Sue  | 19    |
| Sue  | 90    |
| Joe  | 30    |
| Joe  | 15    |
+------+-------+
az3
  • 3,571
  • 31
  • 31
13

If someone is still looking for an answer

select Name,split(Scores,';') as Score
from (
      # replace the inner custome select with your source table
      select *
      from 
      (select 'Bob' as Name ,'10;20;20' as Scores),
      (select 'Sue' as Name ,'14;12;19;90' as Scores),
      (select 'Joe' as Name ,'30;15' as Scores)
);
Elad Ben Akoune
  • 181
  • 2
  • 6
  • 1
    Note: This query only works in Legacy SQL Mode. In Standart SQL, it will give "Column name Name is ambiguous" error. – az3 Nov 22 '18 at 08:27