TSQL count occurrences in multiple columns

Question

I have a table where each column is a question and rows are answers that can assume value from 1 to 4

What is the most efficient way to calculate occurrences of each answer per question?

Input table

q1  q2  q3
1   3   1
2   1   4
1   2   1

Desired Output table

answer  q1  q2  q3

    1   2   0   2
    2   1   1   0
    3   0   1   0   
    4   0   0   1

So far I arrived to the following (for q3 question) but it is just for one question

CREATE TABLE #t
  (
    answer int
  )
insert into #t (answer) values (1)
insert into #t (answer) values (2)
insert into #t (answer) values (3)
insert into #t (answer) values (4)

select * into #q3 from (select q3 as q3,count(*) as occurenceq3
                    from [table]
                    group by q3) as x

select t.answer,tb.occurenceq3 as occurenceq3
from #t t left join #q3 tb on t.answer=tb.Q3

drop table #q3
drop table #t

Answer 1

select answer, q1, q2, q3
from
    q
    unpivot (answer for q in (q1, q2, q3)) as upvt
    pivot (count(q) for q in (q1, q2, q3)) as pvt

I made the mistake of first trying count(*) but I think it makes sense that the aggregation must be explicitly on the column being pivoted, even though I think they would be logically equivalent.

Answer 2

This should work:-

CREATE TABLE #question (q1 int, q2 int, q3 int)
INSERT INTO #question
VALUES
(1,3,1),
(2,1,4),
(1,2,1);

--unpivot to start with
WITH
UNPIVOTED AS
(
SELECT * 
FROM
    (SELECT q1,q2,q3
    FROM #question) p
UNPIVOT
    (answer FOR question in (q1,q2,q3)) AS unpvt
)

--Then pivot
SELECT * FROM 
(SELECT answer, question FROM unpivoted) p
PIVOT
(
COUNT(question)
FOR question IN (q1,q2,q3)
) as pvt