CODEWITHSUNDEEP
sqlhivegoogle-bigquery
ArthurFernandes
ArthurFernandes

Reputation: 11

lateral view explode in bigquery

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.

Upvotes: 1

Views: 2836

Answers (2)

Mike Karp
Mike Karp

Reputation: 1626

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

Upvotes: 0

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173003

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

enter image description here

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

Upvotes: 1

Related Questions