title: Split string into multiple columns with bigquery
body: <p>I have a table in BigQuery with millions of rows, and I want to split adx_catg_id column to multiple new columns. Please note that the adx_catg_id column contains an arbitrary number of words separated by space.</p>
  
  <p>This example of Query below can split the adx_catg_id to multiple columns if the string contains only less than five words. I can extend it to support more number of words, but I need to automate it.</p>
  
  <pre><code>SELECT
    TS, str0, str2, str4, str6, str7
    from
    (select REGEXP_EXTRACT(str5, r'^(.*) .*') as str7
    from
    (select SUBSTR (str5, LENGTH(REGEXP_EXTRACT(str5, r'^(.*) .*')) + 2, LENGTH(str5)) as str6
    from
    (select REGEXP_EXTRACT(str3, r'^(.*) .*') as str5
    from
    (select SUBSTR (str3, LENGTH(REGEXP_EXTRACT(str3, r'^(.*) .*')) + 2, LENGTH(str3)) as str4
    from
    (select REGEXP_EXTRACT(str1, r'^(.*) .*') as str3
    from
    (select SUBSTR (str1, LENGTH(REGEXP_EXTRACT(str1, r'^(.*) .*')) + 2, LENGTH(str1)) as str2
    from
    (select REGEXP_EXTRACT(TS, r'^(.*) .*') as str1
    from
    (select SUBSTR(TS, LENGTH(REGEXP_EXTRACT(TS, r'^(.*) .*')) + 2,LENGTH(TS)) as str0
    from 
    (select adx_catg_id TS from [mydataset.conversions])
    ))))))))
  </code></pre>
  
  <p>How can I loop the above query to generate all words in new columns depending of string length?</p>