如何在SQL服务器中创建数据透视表 [英] How to create pivot table in SQL sever
问题描述
我有一个表（Table_Marks），它包含如下所述的值： -
I have a table (Table_Marks) it contains value as described below: -
CLASS | STD | NAME | SUBJECT | PT1 | PB1 | PT2 | PB2 |
1 | ST1 | NITYA | ENG | 12 | 15 | 30 | 9 |
1 | ST1 | NITYA | HIN | 2 | 22 | 25 | 6 |
1 | ST1 | NITYA | MATH | 3 | 10 | 32 | 8 |
1 | ST2 | SHIV | ENG | AB | AB | 10 | 2 |
1 | ST2 | SHIV | HIN | 2 | 22 | 20 | 1 |
1 | ST2 | SHIV | MATH | 3 | 10 | AB | 5 |
现在我想将它用作PIVOT TABLE，如下所述： -
Now I want to use this as PIVOT TABLE as described below: -
CLASS|STD |NAME |ENG |HIN |MATH |T1 |ENG |HIN |MATH |T2 |T1+T2|
|PT1|PB1 |PT1|PB1|PT1|PB1| |PT2|PB2|PT2|PB2|PT2|PB2|
1 |STD1|NITYA |12 |15 |2 |22 |3 |10 |64 |30 |9 |25 |6 |32 |8 |110 |174
1 |STD2|SHIV |AB |AB |2 |22 |3 |10 |37 |10 |2 |20 |1 |AB |5 |38 |75
请帮助任何人
我尝试过：
。 .................................................. .....................................
Please help any one
What I have tried:
........................................................................................
推荐答案
先生我是初学者
我没有得到任何意义，怎么做...
请帮助...
Sir I am a beginner
I am not getting any point, how to do this...
Please help...
我们不做你的功课：这是有原因的。它就是为了让你思考你被告知的事情，并试着理解它。它也在那里，以便您的导师可以识别您身体虚弱的区域，并将更多的注意力集中在补救措施上。
亲自尝试，你可能会发现它不是和你想的一样困难！
从这开始：使用PIVOT和UNPIVOT [ ^ ]和： SQL Server：PIVOT条款 [ ^ ]
这很复杂，是的 - 但我们只是给你一个解决方案下次没有帮助你。阅读，学习并尝试一下。一旦你了解它就没那么糟了！
如果您遇到特定问题，请询问相关问题，我们会尽力提供帮助。但我们不打算为你做这一切！
We do not do your homework: it is set for a reason. It is there so that you think about what you have been told, and try to understand it. It is also there so that your tutor can identify areas where you are weak, and focus more attention on remedial action.
Try it yourself, you may find it is not as difficult as you think!
Start with this: Using PIVOT and UNPIVOT[^] and this: SQL Server: PIVOT Clause[^]
It's complicated, yes - but our just giving you a solution doesn't help you next time. Read up on it, learn, and give it a try. It's not that bad once you get your head around it!
If you meet a specific problem, then please ask about that and we will do our best to help. But we aren't going to do it all for you!
现在我想要使用它作为如下所述的PIVOT TABLE： -
Now I want to use this as PIVOT TABLE as described below: -
CLASS|STD |NAME |ENG |HIN |MATH |T1 |ENG |HIN |MATH |T2 |T1+T2|
|PT1|PB1 |PT1|PB1|PT1|PB1| |PT2|PB2|PT2|PB2|PT2|PB2|
1 |STD1|NITYA |12 |15 |2 |22 |3 |10 |64 |30 |9 |25 |6 |32 |8 |110 |174
1 |STD2|SHIV |AB |AB |2 |22 |3 |10 |37 |10 |2 |20 |1 |AB |5 |38 |75
你不能！ MS SQL Server不提供插入多个标题行的功能。
另一方面：没有简单的方法 SUM（'AB'，'AB'，'2'，'22'， '3'，'10'）。您需要将文本转换为数字。
You can't! MS SQL Server doesn't provide functionality to insert more than one header row.
On the other hand: there's no simple way to SUM('AB', 'AB', '2', '22', '3', '10'). You'll need to convert text into numbers.
我认为它对您有帮助..
i think it helpful to you..
select CLASS,STD ,NAME,SUBJECT,pt1,pt2,pb1,pb2,t1,t2,gtot
from
(
select class,std,name,subject,pt1,pt2,pb1,pb2,(pt1+pb1)T1,(pt2+pb2)T2,(T1+T2)Gtot
)a
pivot
(
sum(Gtot)
for subject in([Eng],[Hin],..)
)pvt
这篇关于如何在SQL服务器中创建数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
