sql - 查找 SQL 中多列中出现次数最多的情况

Question

我有一个这样的表:

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

我想找到所有四个答案中出现次数最多的，如果出现次数相同，我只需要第一个值。所以理想情况下我应该有一个像这样的输出表:

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

如何在 SQL Server 中执行此操作？我可以跨列按行进行分组吗？

Answer 1

另一种方法，仍然假设存在 Id 列:

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

这是它的 SQLFiddle:http://sqlfiddle.com/#!3/b1dfd/8