创建临时表#Result
if object_id('tempdb..#Result') is not null
drop table #Result
create table #Result
( EmployeeNM nvarchar(50)
,CriterionName nvarchar(200)
,EvalPoint smallint
)
查询结果集添加到临时表
insert into #Result
SELECT H.EmployeeNM EmployeeNM
,CriterionName+'@'+EvalMark
,EvalPoint
FROM [dbTREChinaEvaluation].[dbo].[TB_EvalResult] AS T ---?果表
LEFT JOIN [dbTREChinaEvaluation].[dbo].[TB_T_EvalEmployee] AS M ---?价人??定表
ON T.EvalHistCode=M.EvalHistCode and T.EmployeeCD=M.EmployeeCD ---?价期ID
LEFT JOIN [dbTREChinaEvaluation].[dbo].[TB_T_Criterion] AS D ---考?表
ON D.CriterionCode=T.CriterionCode ---考?表ID
LEFT JOIN [HR_TrialChina].[dbo].[HR_Infor_Personal] AS H
ON H.EmployeeCD=T.EmployeeCD
where EvalEmployeeFlg=@EvalEmployeeFlg and T.EvalHistCode=@EvalHistCode and M.SectionID=@SectionID and M.PositionID=@PositionID
对临时表进行操作，行转列
eclare @sql varchar(8000)
set @sql = ' select EmployeeNM'
select @sql = @sql + ' , sum(EvalPoint) as "合計"'
select @sql = @sql + ' , max(case CriterionName when ''' + CriterionName + ''' then CONVERT(smallint,EvalPoint) else 0 end) [' + CriterionName + ']'
from (select distinct CriterionName from #Result) as a
set @sql = @sql + ' from #Result group by EmployeeNM'
exec(@sql)