CODEWITHSUNDEEP
sqldatabasegoogle-bigquery
user16228179
user16228179

Reputation: 59

SQL: calculate the sum of each column and convert them into rows

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?

Upvotes: 1

Views: 220

Answers (1)

Mikhail Berlyant
Mikhail Berlyant

Reputation: 173191

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

enter image description here

Upvotes: 2

Related Questions