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.
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
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