with cte as( select *,row=row_number()over(partition by sid order by getdate()) from tb ) select a.sid,a.name,prod1=a.prod,series1=a.series,num1=a.num,prod2=b.prod,series2=b.series,num2=b.num from cte a left join cte b on a.name=b.name and b.row=2 where a.row=1
select *,row=row_number()over(partition by sid order by getdate()) from tb
)
select a.sid,a.name,prod1=a.prod,series1=a.series,num1=a.num,prod2=b.prod,series2=b.series,num2=b.num
from cte a left join cte b on a.name=b.name and b.row=2 where a.row=1