Split multiple columns into rows by delimiter comma-CodePudding

I have one table (Bigquery), which has ', ' separated values in two columns:

  id    tag_id    tag_name
    1   1, 2, 4   a, b, d
    2   3         c
    3   1, 4      a, d

For each tag_id is 1:1 matched with a tag_name. I want to split the comma separated values and convert them into rows. The ideal output is like:

id    tag_id    tag_name
1          1    a
1          2    b
1          4    d
2          3    c
3          1    a
3          4    d

My working progress SQL:

SELECT * EXCEPT(t, tn) REPLACE(t AS tag_id, tn AS tags_name)
FROM `table`,
UNNEST(SPLIT(tag_id, ', ')) t, UNNEST(SPLIT(tags_name, ', ')) tn

But it seems that tag_id is not 1:1 matched with tags_name... I'd like to use the EXCEPT in the select query as there are many other columns which I don't want to list them all out manually.

CodePudding user response：

One option is to retaining the offset of each element in each list, so we can use that information to join the relevant rows once they are unnested:

select t.id, elt_id, elt_name
from mytable t
cross join unnest( split(t.tag_id,   ', ') ) elt_id   with offset as rn_id
cross join unnest( split(t.tag_name, ', ') ) elt_name with offset as rn_name
where rn_id = rn_name

CodePudding user response：

Alternatively, you could unnest tag_id and use the offset index to extract tag_name

select  id, 
        elt_id as tag_id ,
        split(tag_name, ',')[safe_offset(rn_id)] as part1
from cte t,unnest(split(t.tag_id,',')) elt_id  with offset as rn_id