I want to do something like this using BigQuery. Input Table

Output Table

got in hive with LATERAL VIEW explode(MAP.

but i can't in bigquery

CodePudding user response：

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

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

CodePudding user response：

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