SQL Server 字符串行转列

declare @t table(ID int,name varchar(10))
insert @t values(1,'张三')
insert @t values(1,'李四')
insert @t values(1,'王五')
insert @t values(2,'赵六')
insert @t values(2,'田七')

;with tb as(
select row=row_number() over(partition by id order by name) ,* from @t)

select 号码=ID,
    姓名1=isnull((select name from tb where id=a.id and row%4=1 and row/4=a.row/4),''),
    姓名2=isnull((select name from tb where id=a.id and row%4=2 and row/4=a.row/4),''),
    姓名3=isnull((select name from tb where id=a.id and row%4=3 and row/4=a.row/4),''),
    姓名4=isnull((select name from tb where id=a.id and row%4=0 and row/4-1=a.row/4),'')
from tb a
group by ID,row/4