create table test (itemname varchar(100),itemresult varchar(100)) insert into test values('标题','生化报告单') insert into test values('建议','随访')declare @sql varchar(1000),@i int select @sql='select convert(varchar,getdate(),111) as time',@i=0 select @i=@i+1,@sql=@sql+',max(case itemname when '''+convert(varchar(100),itemname)+''' then itemname else ''0'' end) [itemname'+cast(@i as varchar(10))+'],max(case itemresult when '''+convert(varchar(100),itemresult)+''' then itemresult else ''0'' end) [itemresult'+cast(@i as varchar(10))+']' from testselect @sql=@sql+' into #a from test' print @sql exec(@sql+' select * from #a')
你的数据如果不确定的话,就要用动态生成SQL的方法select id=identity(int,1,1),itemname,itemresult into #temp from 你的表declare @i int,@ich varchar(30),@sql varchar(8000) select @sql='',@i=max(id) from #tempwhile @i>0 select @ich=cast(@i as varchar(30)) ,@sql=',max(case id when '+@ich+' then itemresult end) as itemresult'+@ich +char(13)+',max(case id when '+@ich+' then itemname end) as itemname'+@ich +char(13)+@sql ,@i=@i-1 set @sql='select '+right(@sql,len(@sql)-1)+' from #temp' exec (@sql)
insert into test values('标题','生化报告单')
insert into test values('建议','随访')declare @sql varchar(1000),@i int
select @sql='select convert(varchar,getdate(),111) as time',@i=0
select @i=@i+1,@sql=@sql+',max(case itemname when '''+convert(varchar(100),itemname)+''' then itemname else ''0'' end) [itemname'+cast(@i as varchar(10))+'],max(case itemresult when '''+convert(varchar(100),itemresult)+''' then itemresult else ''0'' end) [itemresult'+cast(@i as varchar(10))+']' from testselect @sql=@sql+' into #a from test'
print @sql
exec(@sql+' select * from #a')
select @sql='',@i=max(id) from #tempwhile @i>0
select @ich=cast(@i as varchar(30))
,@sql=',max(case id when '+@ich+' then itemresult end) as itemresult'+@ich
+char(13)+',max(case id when '+@ich+' then itemname end) as itemname'+@ich
+char(13)+@sql
,@i=@i-1
set @sql='select '+right(@sql,len(@sql)-1)+' from #temp'
exec (@sql)
先创建动态序列号,再用该序列值做为标识 循环