lateral view explode in bigquery

Question

I want to do something like this using BigQuery.

Input Table

Col1	Col2	Col3	Col4
1	A,B,C	123	789

Output Table

ID	COL	VALUE
1	COL1	1
1	COL2	A,B,C
1	COL3	123
1	COL4	789

I got this in hive with LATERAL VIEW explode(MAP), but I can't get the same in bigquery.

Answer 1

Consider below approach

select id, col, value
from (select *, row_number() over() as id from your_table)
unpivot (value for col in (Col1, Col2, Col3, Col4))       

f apply to sample data in your question

with your_table as (
  select '1' Col1, 'A,B,C' Col2, '123' Col3, '789' Col4
)

output is

Note - this particular approach requires all columns (Col1 - Col4) to be of the same type. If this is not a case you will need first apply cast for some of those to make them string

Answer 2

If it's a discrete number of columns, you can use UNIONs for this...

select id, 'Col1' as Column, col1 as Value
from table
union all
select id, 'Col2' as Column, col2 as Value
from table
union all
select id, 'Col3' as Column, col3 as Value
from table