我现在的数据列表是这个样子的？ 经过查询我想变成这样子的 ，得到两条记录
如白雪 会计电复化1班 经济管理系 第一被评平均分 第二次被评平均分 第三次被评平均分....第十二次被评平均分 总平均分 排名；
曹微微 会计电复化1班 经济管理系 第一被评平均分 第二次被评平均分 第三次被评平均分....第十二次被评平均分 总平均分 排名
------解决方案--------------------
SELECT G.EmpNo, G.EmpName, G.ClassName, G.DeptName, P1.PAverage AS Average_1, P2.PAverage AS Average_2, P3.PAverage AS Average_3, P4.PAverage AS Average_4, P5.PAverage AS Average_5, P6.PAverage AS Average_6, P7.PAverage AS Average_7, P8.PAverage AS Average_8, P9.PAverage AS Average_9, P10.PAverage AS Average_10, P11.PAverage AS Average_11, P12.PAverage AS Average_12, G.Grand_Average, DENSE_RANK() OVER (ORDER BY G.Grand_Average) AS Dense_Rank
FROM
(SELECT EmpNo, EmpName, ClassName, DeptName, CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS Grand_Average
FROM EvaluationList L GROUP BY EmpNo, EmpName, ClassName, DeptName) G
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=1 GROUP BY EmpNo, cshu) P1
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=2 GROUP BY EmpNo, cshu) P2
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=3 GROUP BY EmpNo, cshu) P3
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=4 GROUP BY EmpNo, cshu) P4
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=5 GROUP BY EmpNo, cshu) P5
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=6 GROUP BY EmpNo, cshu) P6
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=1 GROUP BY EmpNo, cshu) P1
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=2 GROUP BY EmpNo, cshu) P2
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=3 GROUP BY EmpNo, cshu) P3
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=4 GROUP BY EmpNo, cshu) P4
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=5 GROUP BY EmpNo, cshu) P5
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=6 GROUP BY EmpNo, cshu) P6
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=1 GROUP BY EmpNo, cshu) P1
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=2 GROUP BY EmpNo, cshu) P2
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=3 GROUP BY EmpNo, cshu) P3
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=4 GROUP BY EmpNo, cshu) P4
OUTER APPLY (SELECT CONVERT(Decimal(5,2),SUM(TotalValue)/COUNT(*)) AS PAverage FROM EvaluationList WHERE EmpNo=G.EmpNo AND cshu=5 GROUP BY EmpNo, cshu) P5