Click here to Skip to main content
15,868,016 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
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      |



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:

........................................................................................
Posted
Updated 19-Mar-18 2:42am
v3
Comments
OriginalGriff 19-Mar-18 6:13am    
And?
What have you tried?
What did it do that you didn't expect, or not do that you did?
What help do you need?
nityanand NCR 19-Mar-18 6:18am    
Could you help please?
OriginalGriff 19-Mar-18 6:25am    
What have you tried?
Where are you stuck?
What help do you need?
nityanand NCR 19-Mar-18 6:29am    
Sir I am a beginner
I am not getting any point, how to do this...
Please help...
#realJSOP 19-Mar-18 8:53am    
You can't pivot that table because some of the values aren't numeric.

Quote:
Sir I am a beginner
I am not getting any point, how to do this...
Please help...

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!
 
Share this answer
 
Quote:
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


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.
 
Share this answer
 
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
 
Share this answer
 
Comments
Maciej Los 19-Mar-18 8:39am    
Sorry, you're wrong. Take a look at input data.
Member 13658881 19-Mar-18 8:42am    
Im not tell the answer just tell try this way..
Maciej Los 19-Mar-18 8:55am    
ANd i'm not a questioner ;)
Member 13658881 20-Mar-18 0:42am    
oh tell something on my query isn't a question?

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900