--Creating Table

Create Table Ex
(cusid int,
 name varchar(3),
 email varchar(20) )


--Inserting Sample Data

Insert Into Ex
Select 111,	'abc',	'abc@xyz.com'
union ALL
Select 222,	'aaa',	'aaa@xyz.com'
union ALL
Select 222,	'aaa',	'aaa1@xyz.com'
union ALL
Select 222,	'aaa',	'aaa2@xyz.com'
union ALL
Select 222,	'aaa',	'aaa3@xyz.com'
union ALL
Select 333,	'bbb',	'bbb@xyz.com'
union ALL
Select 333,	'bbb',	'bbb1@xyz.com'


--Query Using Case

Select cusid, MAX(Name), 
Max(Case When rn = 1 Then email Else '' End) As Email1,
Max(Case When rn = 2 Then email Else '' End) As Email2,
Max(Case When rn = 3 Then email Else '' End) As Email3,
Max(Case When rn = 4 Then email Else '' End) As Email4
From
(Select *, ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As rn From Ex) As a
Group By cusid


--Static Pivot

Select cusid, name, [Email1], [Email2] , [Email3] , [Email4]
From
(Select *, 'Email' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex) As a
Pivot
(max(Email) For rn IN ([Email1],[Email2],[Email3],[Email4])) As pvt
Order By cusid


--Dynamic Pivot

Declare @cols varchar(max), @sql varchar(max)
Declare @temp Table(Cols varchar(max))
Insert Into @temp
Select Distinct 'Email' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex
Select @cols = Coalesce(@cols + ', ', '') + QUOTENAME(cols) From @temp
Set @sql = 'Select cusid, name, '+@cols+'
			From
			(Select *, ''Email'' + Cast(ROW_NUMBER() Over (Partition By cusid Order By (Select NULL)) As varchar) As rn From Ex) As a
			Pivot
			(max(Email) For rn IN ('+@cols+')) As pvt
			Order By cusid'
Execute (@sql)