DECLARE @sql VARCHAR(8000),@groupby VARCHAR(8000)
SET @sql = 'select B.name,C.username,c.mobile,A.[no],convert(varchar(10),A.createDate,121) as createDate'
set @groupby=''
--显示分组的列
SELECT @sql = @sql + ' , max(case A.EID when ''' +convert(varchar(10), A.EID)
+ ''' then A.myAnswer else '''' end )[' + convert(varchar(10), A.EID) + ']'
FROM ( SELECT DISTINCT
EID
FROM WebCastExamResult
) AS a
--后面group by 的列
--SELECT @groupby = @groupby + ' , case A.EID when ''' +convert(varchar(10), A.EID)
-- + ''' then A.myAnswer else '''' end '
--FROM ( SELECT DISTINCT
-- EID
-- FROM WebCastExamResult
-- ) AS a
SET @sql = @sql + ',sum(case isright when 1 then 1 else 0 end) isright from dbo.WebCastExamResult A
left join Config B on A.CID=B.ID
left join [User] C on A.createuserid=C.Id group by B.name,C.username,c.mobile,A.[no],convert(varchar(10),A.createDate,121) '
print @sql
EXEC(@sql)