Create columns where sum other columns based on conditional of other column values?

Question

I need to create columns where sum other columns based on conditional of other column values. The requirement is as below.

I have the following table:

key code1  code2 code3 code4 value1 value2 value3 value4 
0    101   101   101   101   1000    1000  1000   1000    
1    101   101   101   201   1000    1000  1000   1000    
2    101   101   201   201   1000    1000  1000   1000    
3    101   201   201   201   1000    1000  1000   1000    
4    101   201   201   301   1000    1000  1000   1000    
5    101   201   301   301   1000    1000  1000   1000    
6    101   301   301   301   1000    1000  1000   1000    
7    101   101   101   301   1000    1000  1000   1000    
8    101   201   301   0     1000    1000  1000   0       
9    101   301   0     0     1000    1000  0      0       
....

I need to create one column to sum the column value(value1, value2, value3, value4) considering the columns code (code1, code2, code3, code4). The result should be like this:

key  code1 code2 code3 code4 value1 value2 value3 value4 sum_code_101 sum_code_201 sum_code_301
0    101   101   101   101   1000    1000  1000   1000     4000           0           0
1    101   101   101   201   1000    1000  1000   1000     3000           1000        0
2    101   101   201   201   1000    1000  1000   1000     2000           2000        0
3    101   201   201   201   1000    1000  1000   1000     1000           3000        0
4    101   201   201   301   1000    1000  1000   1000     1000           2000        1000
5    101   201   301   301   1000    1000  1000   1000     1000           1000        2000
6    101   301   301   301   1000    1000  1000   1000     1000           0           3000
7    101   101   101   301   1000    1000  1000   1000     3000           0           1000
8    101   201   301   0     1000    1000  1000   0        1000           1000        1000
9    101   301   0     0     1000    1000  0      0        1000           0           1000  

As the real table has 25 different codes (101, 201, 301...) I need to create 25 columns to sum their values.

Any help will be very appreciated.

Answer 1

You could use case expressions:

select
    t.*,
    case when code1 = 101 then value1 else 0 end
        + case when code2 = 101 then value2 else 0 end
        + case when code3 = 101 then value3 else 0 end
        + case when code4 = 101 then value4 else 0 end
        as sum_code_101,
    case when code1 = 201 then value1 else 0 end
        + case when code2 = 201 then value2 else 0 end
        + case when code3 = 201 then value3 else 0 end
        + case when code4 = 201 then value4 else 0 end
        as sum_code_201,
    case when code1 = 301 then value1 else 0 end
        + case when code2 = 301 then value2 else 0 end
        + case when code3 = 301 then value3 else 0 end
        + case when code4 = 301 then value4 else 0 end
        as sum_code_301
from mytable t

You can shorten the syntax a little by unpivoting the rows wirth cross apply, and then using conditional aggregation:

select
    t.*,
    s.*
from mytable t
outer apply (
    select 
        sum(case when code = 101 then val else 0 end) sum_code_101,
        sum(case when code = 201 then val else 0 end) sum_code_201,
        sum(case when code = 301 then val else 0 end) sum_code_301,
        sum(case when code = 401 then val else 0 end) sum_code_401
    from (values 
        (t.code1, t.value1), 
        (t.code2, t.value2), 
        (t.code3, t.value3), 
        (t.code4, t.value4)
    ) as x(code, val)
)  s