Number of TSQL instances in multiple columns

Question

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

What is the most efficient way to count the occurrences of each answer to a 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 have come to the following (for question q3), but this 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 #tt 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 a mistake by trying count(*) , but I think it makes sense that the aggregation should be explicitly indicated on the column, although I think that 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 

Answer 3

If you want the most efficient way, I would suggest putting the index in each of the q columns and running the query as:

 select a.answer, (select count(*) from #question q where q.q1 = a.answer) as q1, (select count(*) from #question q where q.q2 = a.answer) as q2, (select count(*) from #question q where q.q3 = a.answer) as q3 from (select 1 as answer union all select 2 union all select 3 union all select 4 ) a; 

It basically uses indexes to count values ​​and should be pretty fast, faster than aggregating all the unacceptable results.

Answer 4

It will work

 select t1.dis, q1=(select count(q1) from CountAnswers where q1=t1.dis), q2=(select count(q2) from countAnswers where q2=t1.dis), q3=(select count(q3) from countAnswers where q3=t1.dis) from (select dis from( select q1 as dis from CountAnswers union select q2 as dis from CountAnswers union select q3 as dis from CountAnswers)mytab)t1;