id userid managecollect type 管理水平 产品服务 社会责任 品牌服务
1 2 52134 管理水平 5 .... ..... ....
2 3 42135 产品服务 6 .... ..... ....
3 4 21452 社会责任 4 ..... ........ .......
4 2 21452 品牌服务 2 .... ..... ....
select temp.enpid,max(temp.管理水平) 管理水平,max(temp.产品服务) 产品服务,max(temp.品牌服务) 品牌服务,max(temp.社会责任) 社会责任,(max(temp.管理水平)+max(temp.产品服务)+max(temp.品牌服务)+max(temp.社会责任)) 总平均分
from (
select enpid, case when type='管理水平' then (cast(substring(managecollect,1,1) as int)+cast(substring(managecollect,2,1) as int)+cast(substring(managecollect,3,1) as int)+cast(substring(managecollect,4,1) as int)+cast(substring(managecollect,5,1) as int))/5.00 end 管理水平,
case when type='产品服务' then (cast(substring(managecollect,1,1) as int)+cast(substring(managecollect,2,1) as int)+cast(substring(managecollect,3,1) as int)+cast(substring(managecollect,4,1) as int)+cast(substring(managecollect,5,1) as int))/5.00 end 产品服务,
case when type='品牌服务' then (cast(substring(managecollect,1,1) as int)+cast(substring(managecollect,2,1) as int)+cast(substring(managecollect,3,1) as int)+cast(substring(managecollect,4,1) as int)+cast(substring(managecollect,5,1) as int))/5.00 end 品牌服务,
case when type='社会责任' then (cast(substring(managecollect,1,1) as int)+cast(substring(managecollect,2,1) as int)+cast(substring(managecollect,3,1) as int)+cast(substring(managecollect,4,1) as int)+cast(substring(managecollect,5,1) as int))/5.00 end 社会责任
from qybe_manage where enpid=1 or enpid =2
)temp
group by temp.enpid
