September 17, 2008 at 7:38 am
Hi,
Please help me to find out solution of following requirement.
I have two table
1) Student with column (Roll No, Name)
2) Student_Marks with column (Roll No, Subject, Marks)
I need to write a query to give solution in
Rool No Name [P] as Physics, [C] as Chemistry, [M] as maths, Total
Here is data
create table student
( rool_no bigint null,
name varchar(50) null
)
create table student_number
( rool_no bigint null,
subject varchar(50) null,
marks int null
)
insert into student
values (1,'naveen')
insert into student
values (2,'nirmal')
insert into student
values (3,'kamal')
insert into student_number
values(1,'p',50)
insert into student_number
values(1,'c',40)
insert into student_number
values(1,'m',60)
insert into student_number
values(2,'c',40)
insert into student_number
values(2,'m',60)
insert into student_number
values(3,'p',60)
I have written statement
SELECT A.ROOL_NO, A.NAME, PHYSICS, CHEMISTRY, MATHS
FROM STUDENT A
INNER JOIN
(SELECT ROOL_NO, [P] AS PHYSICS,[C] AS CHEMISTRY,[M] AS MATHS
FROM
(SELECT ROOL_NO,SUBJECT,MARKS
FROM STUDENT_NUMBER ) STD_NUM
PIVOT (SUM(MARKS) FOR SUBJECT IN ([P],[M],[C]) ) AS PVT) AS B
ON A.ROOL_NO = B.ROOL_NO
and it will give initial five column.
Rool Name Physics Chemistry Maths
1 naveen 50 40 60
2 nirmal NULL 40 60
3 kamal 60 NULL NULL
How to add total column in last.
Could someone pleas tell me where to add group by.
Result should be
Rool Name Physics Chemistry Maths Total
1 naveen 50 40 60 150
2 nirmal NULL 40 60 100
3 kamal 60 NULL NULL 60
Thanks!
Naveen
September 17, 2008 at 7:45 am
Hi,
I added one more join and it workf fine
SELECT A.ROOL_NO, A.NAME, PHYSICS, CHEMISTRY, MATHS, TOTAL
FROM STUDENT A
INNER JOIN
(SELECT ROOL_NO, [P] AS PHYSICS,[C] AS CHEMISTRY,[M] AS MATHS
FROM
(SELECT ROOL_NO,SUBJECT,MARKS
FROM STUDENT_NUMBER ) STD_NUM
PIVOT (SUM(MARKS) FOR SUBJECT IN ([P],[M],[C]) ) AS PVT) AS B
ON A.ROOL_NO = B.ROOL_NO
INNER JOIN (SELECT ROOL_NO,SUM(MARKS) AS TOTAL FROM STUDENT_NUMBER GROUP BY ROOL_NO ) AS C
ON C.ROOL_NO = A.ROOL_NO
But will any other way whih can add group by in pivot itself.
open for suggestion.
Thanks!
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply