創(chuàng)建臨時表#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)

SQL行轉列