DECLARE @t TABLE(ID INT, Name VARCHAR(10), Mode VARCHAR(10))
INSERT INTO @t(ID,Name,Mode)
VALUES(1, 'AAAAAAA','Phone'),
(2, 'AAAAAAA','Phone'),
(3, 'AAAAAAA','Phone'),
(4, 'BBBBB','SMS'),
(5, 'BBBBB','SMS'),
(6, 'CCCCC','Email'),
(7, 'AAAAAAA','SMS');

SELECT Name,
       SUM(CASE WHEN Mode='Phone' THEN 1 ELSE 0 END) AS Phone,
       SUM(CASE WHEN Mode='SMS' THEN 1 ELSE 0 END) AS SMS,
       SUM(CASE WHEN Mode='Email' THEN 1 ELSE 0 END) AS Email
FROM @t
GROUP BY Name
ORDER BY Name;

select name,[Phone],[Sms],[Email]
from  
(
select  name , mode , sum(case 
when name = 'AAAAA' then 1 
when name = 'BBBBB' then 1 
when name = 'CCCCC' then 1 else 0 end) as cnt from @t 
group by mode, name
)t 
pivot
( sum (cnt)
for mode in ([Email],[Phone],[Sms])
)pvt
ORDER BY Name;

--Creating Table 

Create table Ex1 
(
	ID Int, 
	Name Varchar(30), 
	Mode Varchar(30) 
) 


--Inserting Sample Data

Insert into Ex1 
Select 1, 'AAAAAAA',	'Phone' 
Union ALL
Select 2, 'AAAAAAA',	'Phone' 
Union ALL
Select 3, 'AAAAAAA',	'Phone'
Union ALL
Select 4, 'BBBBB',		'SMS'
Union ALL
Select 5, 'BBBBB',		'SMS'
Union ALL
Select 6,'CCCCC',		'Email'
Union ALL
Select 7, 'AAAAAAA',	'SMS' 


--Dynamic Pivot 

Declare @sql Varchar(Max) 
Set @sql = 'Select Name, ' 
Select @sql = @sql + STUFF((Select ',SUM(Case When mode = ' + CHAR(39) + Mode + CHAR(39) + ' Then 1 Else 0 End) As ' + Mode From (Select Distinct Mode From Ex1) As a FOR XML Path('')),1,1,'') 
Select @sql = @sql + ' From Ex1 Group By Name' 
Execute (@sql)