My current table includes more than 100 columns and I need to calculate the sum of each column and convert them into rows. Since there are more than 100 column, it is not convenient to use the unpivote clause. Is there any other way to do that? Below is a snapshot of the original table:
| col1 | col2 |
|---|---|
| 23 | 44 |
| 33 | 45 |
and here is what i need:
| variable | sum_variable |
|---|---|
| col1 | 56 |
| col2 | 89 |
Can you help me with this please?
Advertisement
Answer
Consider below
select split(kv, ':')[offset(0)] as variable,
sum(cast(split(kv, ':')[offset(1)] as int64)) as sum_variable
from data t, unnest(split(translate(to_json_string(t), '{}"', ''))) kv
group by variable
if applied to sample data in your question – output is
