纵横表转交叉表

--exec sp_getcrossdata

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
--exec sp_getcrossdata
--alter  proc sp_getcrossdata as
--begin
 declare @SQLString nvarchar(4000)
 declare @temp_cybh varchar(50)
 declare @temp_gzyd varchar(511)

 select 用户名,姓名 into #tmp1 from 用户表
 select 词语编号,工作分类+'|'+工作要点 as 工作要点 into #tmp2 from 词语视图
 select 用户名,词语编号,sum(工作时) as 工作时 into #tmp3 from 日志明细表 where 项目编号='0000000' group by 用户名,词语编号 order by 词语编号

 set @SQLString = N'select 姓名,'
 
 declare cursor_xkb cursor for
 select distinct a.词语编号,工作要点 from #tmp3 as a,#tmp2 as b
 where a.词语编号=b.词语编号
 open cursor_xkb
 fetch next from cursor_xkb into @temp_cybh,@temp_gzyd
 while @@fetch_status=0
 begin
  set @SQLString=@SQLString+N'isnull(sum(case b.词语编号 when '''
  +cast(@temp_cybh as varchar)+N''' then 工作时 end),0)
  as "'+@temp_gzyd+'",'
  fetch next from cursor_xkb into @temp_cybh,@temp_gzyd
  
 end
 close cursor_xkb
 deallocate cursor_xkb
 set @SQLString = left(@SQLString,len(@SQLString)-1)
 set @SQLString = @SQLString +N'from #tmp1 as a,#tmp3 as b
 where a.用户名=b.用户名 group by 姓名'
 print @SQLString
 exec sp_executesql @SQLString 
 drop table #tmp1,#tmp2,#tmp3
--end

 

 

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO