With your very small sample set and lack of rules, this will get you what you want.
select [id], name,
case when [id] % 3 = 1 then 1 end as col1,
case when [id] % 3 = 2 then 2 end as col2,
case when [id] % 3 = 0 then 3 end as col3
from names
If you need more Col
n columns, or there are more than three of each name, then it won't work.
If you;re trying to say that "ID is the
nth instance of Name" then you might need to parse the info into a temp table and look at pivoting the results to get the col
n.