How to count the number of occurences for a specific value from a range of columns using T-SQL?

I have a table (t1) as shown below that contains single-character values in all columns named v%:

Id    v1  v2   v3  v4  v5  v6
100    O   O    E   O  E   E
103    E   O    E   E  O   E
...

I need to count the number of occurrences for “O” and “E” and display them in 2 additional columns. How can I do this?

Expected Output:

Id    v1  v2   v3  v4  v5  v6  O   E
100    O   O    E   O  E   E   3   3
103    E   O    E   E  O   E   2   4
...

Answer

I would use apply and aggregation:

select t.*, v.*
from t cross apply
     (select sum(case when v.v = 'O' then 1 else 0 end) as num_os,
             sum(case when v.v = 'E' then 1 else 0 end) as num_es
      from (values (t.v1), (t.v2), (t.v3), (t.v4), (t.v5), (t.v6)) v(v)
     ) v;