如何在sql server中的单个pivot中进行两次求和 [英] how to make two sum in single pivot in sql server

问题描述

Hi,

SELECT * FROM (SELECT staffid as [staffid], Question ,Self_Evaluation FROM @temptable)
  as s PIVOT (SUM(s.Self_Evaluation)FOR [staffid] IN ([1018], [1049], [1012], [1080],[1082]))AS temp1

SELECT * FROM (SELECT staffid as [staffid], Question ,Management_Evaluation FROM @temptable)
as s PIVOT(SUM(s.Management_Evaluation) FOR [staffid] IN ([1018], [1049], [1012], [1080],[1082]))AS temp2

这是我的查询。我想加入这两个支点并且出来应该是这样的



问题1082-self 1082-mngmt 1049-self 1049-mngmt

工作效率9 10 8 5



请帮助我

this is my query. I want to join these two pivot and out come should be like this

questions 1082-self 1082-mngmt 1049-self 1049-mngmt
job efficiency 9 10 8 5

Please help me

推荐答案

我建​​议使用一些东西类似于：

I'd suggest to use something similar to this:

SELECT Question, TypeOfEvaluation, [1018], [1049], [1012], [1080], [1082]
FROM (
    SELECT staffid as [staffid], Question, 'self' As TypeOfEvaluation, Self_Evaluation As [Efficiency]
    FROM @temptable
    UNION ALL
    SELECT staffid as [staffid], Question, 'mngmt' AS As TypeOfEvaluation, Management_Evaluation  As [Efficiency]
    FROM @temptable
) AS DT
PIVOT (SUM([Efficiency]) FOR [staffid] IN ([1018], [1049], [1012], [1080], [1082]))AS temp1

以上查询应返回：

Above query should return:

Question | TypeOfEvaluation | 1082 | 1049 |
 q1?     |      self        | 9    |    8 |
 q1?     |     mngmt        | 10   |    5 |

这篇关于如何在sql server中的单个pivot中进行两次求和的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！