SQL: подсчет и группировка по одному столбцу с условиями

Оператор PIVOT выполнит работу

select  *
from    tbl t
        pivot
        (
            count(col2)
            for col2 in ([A], [B], [C])
        ) p

вы можете использовать случай, когда

with t1 as
(
select id,col2,count(*) as cnt from t
group by id,col2
) select id, max(case when col2='A' then  cnt end) A_count
         max(case when col2='B' then  cnt end) B_count,
         max(case when col2='C' then  cnt end) C_count from t1
         group by id

Попробуй это

SELECT [Col 1],
       COUNT(CASE WHEN [COL 2]='A' THEN 1  ELSE 0 END) [A COUNT],
       COUNT(CASE WHEN [COL 2]='B' THEN 1  ELSE 0 END) [B COUNT],
       COUNT(CASE WHEN [COL 2]='C' THEN 1  ELSE 0 END) [C COUNT]
FROM TableName
GROUP BY [Col 1]

Вы можете попробовать использовать pivot

SELECT col1 ,   
[A], [B], [C]
FROM  
(SELECT col1, col2
    FROM tablename) AS SourceTable  
PIVOT  
(  
sum(col2)  
FOR col2 IN ([A], [B], [C])  
) AS PivotTable

Рекомендую PIVOT. См. Следующую демонстрацию:

SELECT * INTO #t FROM (
    VALUES 
    (1, 'A'),
    (1, 'B'),
    (1, 'B'),
    (2, 'C'),
    (2, 'A'),
    (2, 'C'),
    (2, 'B')) T([COL 1],[COL 2])

SELECT [COL 1], A [A count], B [B count], C [C count]
FROM #t
PIVOT (COUNT([COL 2]) FOR [COL 2] IN (A,B,C)) P

Результат

COL 1       A count     B count     C count
----------- ----------- ----------- -----------
1           1           2           0
2           1           1           2

Вы даже можете сделать это, выполнив динамический SQL-запрос. Это поможет избежать явного написания кода.

Запрос

declare @sql as varchar(max);

select @sql = 'select [col_1], ' + stuff((
        select distinct ',sum(case [col_2] when ' + char(39) + [col_2] + char(39) 
        + ' then 1 else 0 end) as [' + [col_2] + ' Count]'
        from [dbo].[your_table_name]
        for xml path('')
    )
    , 1, 1, ''
);

select @sql += 'from [dbo].[your_table_name] group by [col_1];';

exec(@sql);

Я бы сделал условную агрегацию вместо PIVOT:

SELECT col1,
       SUM(CASE WHEN col2 = 'A' THEN 1 ELSE 0 END) AS A_cnt,
       SUM(CASE WHEN col2 = 'B' THEN 1 ELSE 0 END) AS B_cnt,
       SUM(CASE WHEN col2 = 'C' THEN 1 ELSE 0 END) AS C_cnt
FROM table t
GROUP BY col1;