declare @temp table (id int , Supplier varchar(5) )
Insert into @temp values (1,'1'),(2,'2'),(2,'3'),(3,'a'),(3,'b'),(3,'c')
Select * from (
Select id
,supplier
,ROW_number()over(partition by id order by id) as rw
from @temp
)a
Pivot(
Min(Supplier)
for rw in ([1],[2],[3])
) as Pvttable
Is there a guaranteed maximum number of Suppliers that a given ID can ever have? And do you want a column for each supplier that an ID might have. For example, would the line for ID #3 have the supplier ids as a single column with all of them separated by spaces or would there be a total of 3 supplier columns?
It would also help if we knew why you wanted to denormalize the data this way.