I have my student table as follows
rno sub mark
1 math 98
1 phy 78
1 che 79
2 math 57
2 phy 97
2 che 69
.
.
...
I need the out put
roll_no physics chemistry math
1 78 79 98
2 97 67 57
can we pivot the table using sql query. can any one help??
(6 posts) (4 voices)-
remni_rash
Member@snt_cool,
You can use the PIVOT and UNPIVOT relational operators to change a table-valued expression into another table or sub-result set.But as per your requirement, use the below query:
SELECT rno as "roll_no",[phy] as "physics", [che] as "chemistry",[math] as "math" FROM (SELECT rno, sub, mark from dbo.Student) AS p PIVOT ( sum (mark) FOR sub IN ([che],[math],[phy] ) ) AS pvtHope this will help you out from your problem!
Posted 1 year ago # -
Hi sachin,
I guess u didn't copied the code correctly. I personally checked the query in SSMS.
The query is 100% correct.Please try to run in SSMS (SQL Server), if you are running this query on Oracle with some other application, then you have specify what you are using, then only I can suggest you something.
Thanks Arun
Posted 1 year ago # -
Hi.. thanks arun.. it is working fine.. and in between i got another answer.. so thought to post it..
-----------------------------------------------------------------------
Select ROLL, max(case when sub=’MATH’ then MARK end;) ‘MATH’,
max(case when sub=’PHYSICS’ then MARK end;) ‘PHY’,
max(case when sub=’CHEM’ then MARK end;) ‘CHEM’
from STUEDNT
group by ROLL;
------------------------------------------------------------------------Posted 1 year ago #
Reply
You must log in to post.
