0

I am trying to use the SPLIT() function to convert an array of strings into individual rows.

The data looks something like this:

id   ticket
1    1,2,3,4,5,6,7,8,9...etc.
2    11,12,13,14,15,16,17,18,19...etc.
3    21,22,23,24,25,26,27,28,29...etc.

I would want the data to look like this:

id   ticket
1    1
1    2
1    3
1    4
1    5
1    6
1    7
1    8
1    9
2    11
2    12
2    13
2    14
2    15
2    16

...and so on. When I use the following: SELECT id, SPLIT(ticket) FROM table, I get exactly the result I want. The problem is, I have to join this table with another table which is partitioned and I cannot use legacy SQL. Because of the multiple tickets associated with each id (up to 200), typing out each one seems inefficient. The ticket numbers are also formatted as a string.

If anyone has a solution for this, it would be greatly appreciated!

Ava O'Neill
  • 1

1 Answers1

2

Below example for BigQuery Standard SQL 

#standardSQL
SELECT id, CAST(ticket AS INT64) ticket
FROM `project.dataset.table`,
UNNEST(SPLIT(ticket)) ticket

You can test, play with above using sample data from your question as in example below 

#standardSQL
WITH `project.dataset.table` AS (
  SELECT 1 id, '1,2,3,4,5,6,7,8,9' ticket UNION ALL
  SELECT 2, '11,12,13,14,15,16,17,18,19' UNION ALL
  SELECT 3, '21,22,23,24,25,26,27,28,29' 
)
SELECT id, CAST(ticket AS INT64) ticket
FROM `project.dataset.table`,
UNNEST(SPLIT(ticket)) ticket

with result 

Row id  ticket   
1   1   1    
2   1   2    
3   1   3    
4   1   4    
5   1   5    
6   1   6    
7   1   7    
8   1   8    
9   1   9    
10  2   11   
11  2   12   
12  2   13   
13  2   14   
14  2   15   
15  2   16   
16  2   17   
17  2   18   
18  2   19   
19  3   21   
20  3   22   
21  3   23   
22  3   24   
23  3   25   
24  3   26   
25  3   27   
26  3   28   
27  3   29
Mikhail Berlyant
  • 165,386
  • 8
  • 154
  • 230