Split multiple columns into rows by delimiter comma

Question

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.

GMB · Accepted Answer · 2022-11-09 20:41:21Z

2

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

answered Nov 9, 2022 at 20:41

GMB

224k25 gold badges103 silver badges151 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Rajat · Accepted Answer · 2022-11-09 22:05:32Z

0

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

answered Nov 9, 2022 at 22:05

Rajat

5,8733 gold badges14 silver badges30 bronze badges

Collectives™ on Stack Overflow

Split multiple columns into rows by delimiter comma

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest