Counting values within the same row in SQL Server

My SQL Server problem is as follows: let’s say we have clients and we wish to rate them based on 4 categories (Score_1…Score_4) on a scale of 0 to 2. I have a table presented below:

What I want my code to do is count the number of 0, 1, and 2 values each of my clients recieved. The result table I would like to get would like this:

So client_1 got two 0 scores, one 1 score and one 2 score, client_2 got one 0 score, one 1 score and two 2 scores. Any suggestions? Thanks in advance!

Answer

You can unpivot, then do conditional aggregation:

select t.id, 
    sum(case when x.score = 0 then 1 else 0 end) cnt_0,
    sum(case when x.score = 1 then 1 else 0 end) cnt_1,
    sum(case when x.score = 2 then 1 else 0 end) cnt_2
from mytable t
cross apply (values (score_1), (score_2), (score_3), (score_4)) x(score)
group by t.id