I have the following table:
col_a | col_b | col_c |
---|---|---|
1 | 0 | 1 |
0 | 1 | 0 |
I want to sum up each column and assign a value to it based on the output, with the output like-so below:
column | sum | string |
---|---|---|
col_a | 1 | ‘string one’ |
col_b | 1 | ‘string two’ |
col_b | 1 | ‘string three’ |
I’ve tried the following SQL:
SELECT SUM(col_a) AS sum_a, SUM(col_b) AS sum_b, SUM(col_c) AS sum_c FROM table
This gives me three total columns, but I’ve been unable to figure out the rest.
Greatly appreciated!
Advertisement
Answer
You really want to unpivot. I typically use cross apply for that.
select ca.key,SUM(ca.pair) from table cross apply(values('col_a',col_a),('col_b',col_b),('col_c',col_c)) ca(key,pair) group by ca.key
I don’t understand your string column.