行列转置加where条件
以下代码没有问题，但需要加where限制条件,如where Name='张三'。加上去始终报语法错。

declare @sql varchar(8000)
select @sql = isnull(@sql + '],[' , '') + Class from Elearning_AnswerSheet group by Class 
set @sql = '[' + @sql + ']'
exec ('select m.* , n.平均分 from
(select * from (select TopicID,Store,Name,Position,Code,Mark,Class from Elearning_AnswerSheet) a pivot (AVG(Mark) for Class in (' + @sql + ')) b) m ,  
(select TopicID,Store,Name,Code,Position,cast(avg(Mark*1.0) as decimal(18,2)) as 平均分 from Elearning_AnswerSheet group by TopicID,Store,Name,Code,Position) n
where m.Store = n.Store and m.Name=n.Name and m.Code=n.Code and m.Position=n.Position')

------解决方案--------------------

SQL code

select @sql = isnull(@sql + '],[' , '') + Class from Elearning_AnswerSheet group by Class  

set @sql = '[' + @sql + ']'

exec ('select m.* , n.平均分 from
(select * from (select TopicID,Store,Name,Position,Code,Mark,Class from Elearning_AnswerSheet) a pivot (AVG(Mark) for Class in (' + @sql + ')) b) m ,   
(select TopicID,Store,Name,Code,Position,cast(avg(Mark*1.0) as decimal(18,2)) as 平均分 from Elearning_AnswerSheet group by TopicID,Store,Name,Code,Position) n
where m.Store = n.Store and m.Name=n.Name and m.Code=n.Code and m.Position=n.Position and m.Name=''张三'' ')