Как выбрать количество нескольких столбцов

Вам может понадобиться сделать unpivot и pivot:

SELECT Field, 
       SUM(CASE WHEN choice = '1stChoice' THEN 1 ELSE 0 END) AS [1stChoice],
       SUM(CASE WHEN choice = '2Choice' THEN 1 ELSE 0 END) AS [2stChoice],
       SUM(CASE WHEN choice = '3rdChoice' THEN 1 ELSE 0 END) AS [3stChoice]
FROM myView mv CROSS APPLY
     ( VALUES ('1stChoice', [1stChoice]), ('2Choice', [2Choice]), ('3rdChoice', [3rdChoice])
     ) mvv (choice, Field)
GROUP BY Field;

Вы можете попробовать что-то вроде следующего, хотя производительность может резко возрасти, если у вас много записей из-за соединения с несколькими OR, и определенно не подходит для масштабирования, если вы планируете иметь много возможных вариантов (не ответов).

IF OBJECT_ID('tempdb..#myView') IS NOT NULL
    DROP TABLE #myView

CREATE TABLE #myView (
    ID INT IDENTITY,
    [1stChoice] VARCHAR(10),
    [2ndChoice] VARCHAR(10),
    [3rdChoice] VARCHAR(10))

INSERT INTO #myView (
    [1stChoice],
    [2ndChoice],
    [3rdChoice])
VALUES
    ('AA', 'BB', 'CC'),
    ('CC', 'BB', 'AA'),
    ('AA', 'CC', 'BB'),
    ('BB', 'AA', 'CC'),
    ('AA', 'CC', 'BB')


;WITH AvailableAnswers AS
(
    SELECT T.[1stChoice] AS Field FROM #myView AS T
    UNION
    SELECT T.[2ndChoice] FROM #myView AS T
    UNION
    SELECT T.[3rdChoice] FROM #myView AS T
)
SELECT
    Field = A.Field,
    [1stChoice] = COUNT(CASE WHEN V.[1stChoice] = A.Field THEN 1 END),
    [2ndChoice] = COUNT(CASE WHEN V.[2ndChoice] = A.Field THEN 1 END),
    [3rdChoice] = COUNT(CASE WHEN V.[3rdChoice] = A.Field THEN 1 END)
FROM
    AvailableAnswers AS A
    INNER JOIN #MyView AS V ON 
        A.Field = V.[1stChoice] OR
        A.Field = V.[2ndChoice] OR
        A.Field = V.[3rdChoice]
GROUP BY
    A.Field

Результат:

Field   1stChoice   2ndChoice   3rdChoice
AA      3           1           1
BB      1           2           2
CC      1           2           2

Пример использования сводки;

SELECT [ChosenValue], [pvt].[1stChoice], pvt.[2ndChoice], pvt.[3rdChoice]
FROM (
    SELECT Id, '1stChoice' Choice, [1stChoice] [ChosenValue]
    FROM MyView
    UNION
    SELECT Id, '2ndChoice' Choice, [2ndChoice]
    FROM MyView
    UNION
    SELECT Id, '3rdChoice' Choice, [3rdChoice]
    FROM MyView
) AS tbl
PIVOT (
    COUNT(Id)
    FOR Choice IN ([1stChoice], [2ndChoice], [3rdChoice])
) as pvt

Результаты;

ChosenValue 1stChoice   2ndChoice   3rdChoice
AA          3           1           1
BB          1           2           2
CC          1           2           2

используя С# linq:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;

namespace ConsoleApplication98
{
    class Program
    {
        static void Main(string[] args)
        {

            DataTable dt = new DataTable();
            dt.Columns.Add("ID", typeof(int));
            dt.Columns.Add("1stChoice", typeof(string));
            dt.Columns.Add("2Choice", typeof(string));
            dt.Columns.Add("3rdChoice", typeof(string));

            dt.Rows.Add(new object[] { 1, "AA", "BB", "CC" });
            dt.Rows.Add(new object[] { 2, "CC", "BB", "AA" });
            dt.Rows.Add(new object[] { 3, "AA", "CC", "BB" });
            dt.Rows.Add(new object[] { 4, "BB", "AA", "CC" });
            dt.Rows.Add(new object[] { 5, "AA", "CC", "BB" });

            string[] fields = dt.AsEnumerable().SelectMany(x => x.ItemArray.Skip(1).Select(y => (string)y)).Distinct().OrderBy(x => x).ToArray();

            DataTable pivot = new DataTable();
            pivot.Columns.Add("Field", typeof(string));
            pivot.Columns.Add("1stChoice", typeof(string));
            pivot.Columns.Add("2Choice", typeof(string));
            pivot.Columns.Add("3rdChoice", typeof(string));

            foreach (string field in fields)
            {
                int firstChoice = dt.AsEnumerable().Where(x => x.Field<string>("1stChoice") == field).Count();
                int secondChoice = dt.AsEnumerable().Where(x => x.Field<string>("2Choice") == field).Count();
                int thirdChoice = dt.AsEnumerable().Where(x => x.Field<string>("3rdChoice") == field).Count();
                pivot.Rows.Add(new object[] { field, firstChoice, secondChoice, thirdChoice }); 
            }

        }
    }

}

Вы можете попробовать следующий запрос.

 SELECT t1.1stChoice AS Field,
  (SELECT COUNT(*) FROM myView t2 WHERE t2.1stChoice= t1.1stChoice) 1stChoice,
  (SELECT COUNT(*) FROM myView t3 WHERE t3.2Choice  = t1.1stChoice) 2Choice  ,
  (SELECT COUNT(*) FROM myView t4 WHERE t4.3rdChoice= t1.1stChoice ) 3rdChoice
FROM myView t1
GROUP BY t1.1stChoice