BigQuery SPLIT manual creation of table

Question

In reference to this question I would like to create multiple columns manually.

SELECT SPLIT(Titles) AS Title 
FROM (SELECT 'Title 1,Title 2,Title 3,Title 4' AS Titles)

I have tried simply adding new column like this:

SELECT SPLIT(Titles) AS Title, SPLIT(Names) AS Name,FROM (SELECT 'Title 1,Title 2,Title 3,Title 4' AS Titles, 'Name 1,Name 2,Name 3,Name 4' AS NAMES)

However BQ shows me following error:

Error: Cannot output multiple independently repeated fields at the same time. Found Title and Name

I think it could be related how BQ flatten the results and I found similar problem here. Unfortunately I am not able to transform my code. I can use just Legacy SQL.

EDIT: Expected table should look like this:

-- +---------+--------+
-- | Title   | Name   |
-- +---------+--------+
-- | Title 1 | Name 1 |
-- | Title 2 | Name 2 | 
-- | Title 3 | Name 3 |
-- | Title 4 | Name 4 |
-- +---------+--------+

can you provide what exactly result you expect as it can vary — Mikhail Berlyant, Jul 13 '17 at 14:09
I edited the post. Basically it should add the column with the same number of rows. — John Snow, Jul 13 '17 at 14:16

Mikhail Berlyant · Accepted Answer · 2017-07-13T14:42:03.783

Below is for BigQuery Standard SQL

#standardSQL
WITH data AS (
  SELECT 'Title 1,Title 2,Title 3,Title 4' AS Titles, 'Name 1,Name 2,Name 3,Name 4' AS Names
)
SELECT 
  Title, 
  Name
FROM data, 
  UNNEST(SPLIT(Titles)) AS Title WITH OFFSET AS pos1, 
  UNNEST(SPLIT(Names)) AS Name WITH OFFSET AS pos2
WHERE pos1 = pos2  
ORDER BY Title

Same idea in BigQuery Legacy SQL looks a little more bushy

#legacySQL
SELECT
  Title, Name
FROM FLATTEN((
  SELECT Title,  POSITION(Title) AS pos1
  FROM (
    SELECT SPLIT(Titles) AS Title
    FROM (SELECT 'Title 1,Title 2,Title 3,Title 4' AS Titles, 'Name 1,Name 2,Name 3,Name 4' AS Names)
  )
), pos1) AS titles
JOIN FLATTEN((
  SELECT Name, POSITION(Name) AS pos2
  FROM (
    SELECT SPLIT(Names) AS Name
    FROM (SELECT 'Title 1,Title 2,Title 3,Title 4' AS Titles, 'Name 1,Name 2,Name 3,Name 4' AS Names)
  )
), pos2) AS names
ON pos1 = pos2

@johnsnow - did you have chance to try? – Mikhail Berlyant Jul 19 '17 at 05:08 — Mikhail Berlyant, Jul 19 '17 at 05:08

BigQuery SPLIT manual creation of table

1 Answers1