ID ProductName UnitPrice
declare @sql varchar(8000),@i int,@ich varchar(20)
select @sql=''
,@i=max(cc),@ich=cast(@i as varchar),@i=@i-1
while @i>=0
select @sql=',UnitPrice'+cast(@i+1 as varchar)+'=max(case myid%'+@ich+' when '+cast(@i as varchar)+' then UnitPrice end)'+@sql,@i=@i-1
set @sql='select ID,ProductName'+@sql+' from #tb gruop by ID,ProductName'
select myid=identity(int,1,1),* into #tb from 原始表
exec(@sql)
drop table #tb
select identity(int,1,1)xh,cast(id as int) id,productname,unitprice into #t from 表
select ID,ProductName
,sum(case when (xh-1)%3=0 then unitprice end ) unitprice1
,sum(case when (xh-1)%3=1 then unitprice end ) unitprice2
,sum(case when (xh-1)%3=2 then unitprice end ) unitprice3
from #t group by ID,ProductName