sql server - How to calculate totals across columns with muliple criteria? -

sql server - How to calculate totals across columns with muliple criteria? -

i'm trying add together totals per grade level, of each programme code. there more 26 programme codes total, , scattered on 6 columns. need total programme codes school, grade, output shows.

i'm sorry have no code, don't know start on this.

dataset:

output desired:

the totals on output grouped school, gradelevel.

can sql this, if so, how please?

thank you!

this should started

select school_code ,grade_level ,sum(case when [program code 1] = 'a' or [program code 2] = 'a' or [program code 3] = 'a' or [program code 4] = 'a' or [program code 5] = 'a' or [program code 6] = 'a' 1 else 0 end )as ,sum(case when [program code 1] = 'b' or [program code 2] = 'b' or [program code 3] = 'b' or [program code 4] = 'b' or [program code 5] = 'b' or [program code 6] = 'b' 1 else 0 end )as b ,sum(case when [program code 1] = 'c' or [program code 2] = 'c' or [program code 3] = 'c' or [program code 4] = 'c' or [program code 5] = 'c' or [program code 6] = 'c' 1 else 0 end )as c --... repeat above or many programme codes have --whatever table need utilize grouping school_code ,grade_level

sql-server tsql