Here is a pivot approach :
declare @data table (name varchar(50),[group] varchar(50))
insert into @data (NAME, [GROUP])
values ('AAA', 'A'), ('AAA', 'B'), ('AAA', 'A'), ('AAA', 'C'), ('BBB', 'A'), ('BBB', 'B'), ('BBB', 'B')
select name, A, B, C
from (select name, [group], count([group]) count from @data group by name, [group]) src
pivot (sum([count]) FOR [group] in ( [A], [B], [C])) pvt
Good Luck