Find most occurrences in multiple columns in SQL

Question

I have a table like this:

Answer1,Answer2,Answer3,Answer4 A,B,C,CB,D,D,DC,C,A,CB,B,D,D 

I want to find most cases for all four answers, if the number of occurrences is the same, I just need the first value. Therefore, ideally, I should have an output table as follows:

 Answer1,Answer2,Answer3,Answer4,MostAnswers,Occurrences A,B,C,C,C,2 B,D,D,D,D,3 C,C,A,C,C,3 B,B,D,D,B,2 

How to do this on sql server? Can I make a group column by row?

Answer 1

Another approach that still assumes an Id column is:

 select a.Id, a.Answer1, a.Answer2, a.Answer3, a.Answer4, TopAnswers.* from AnswerTable a outer apply ( select top 1 Answer, count(*) as cnt from ( select Answer1 as Answer from AnswerTable where Id = a.Id union all select Answer2 from AnswerTable where Id = a.Id union all select Answer3 from AnswerTable where Id = a.Id union all select Answer4 from AnswerTable where Id = a.Id ) x group by Answer order by count(*) desc, Answer asc ) TopAnswers 

Here's the SQLFiddle for it: http://sqlfiddle.com/#!3/b1dfd/8

Answer 2

One way is to disable data. The trick is that you need id identify each row. This is achieved using row_number() in the following query. Then obtaining the required values ​​requires a reasonable use of the aggregation and window functions:

 with t as ( select (case when nn = 1 then answer1 when nn = 2 then answer2 when nn = 3 then answer3 when nn = 4 then answer4 end) as answer, a.* from (select row_number() over (order by (select NULL)) as recnum, a.* from answers a ) cross join (select 1 as n union all select 2 union all select 3 union all select 4) n ) select answer1, answer2, answer3, answer4, answer, cnt from (select recnum, answer1, answer2, answer3, answer4, answer, count(*) as cnt, row_number() over (partition by recnum order by count(*) desc ) as seqnum_cnt from t group by recnum, answer1, answer2, answer3, answer4, answer ) a where seqnum_cnt = 1;