以下代码没有问题，但需要加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) nwhere m.Store = n.Store and m.Name=n.Name and m.Code=n.Code and m.Position=n.Position and m.Name=''张三'' ')