First of all, please, read my comment [EIDT] and OriginalGriff's answer[/EDIT].
Here is an idea, how to achieve that without using pivot.
CASE WHEN ... END
solution:
SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5])
FROM (
SELECT fStudentname,
EC1 = SUM(CASE WHEN FSubject = 'EC1' THEN Fmarks ELSE NULL),
EC2 = SUM(CASE WHEN FSubject = 'EC2' THEN Fmarks ELSE NULL),
EC3 = SUM(CASE WHEN FSubject = 'EC3' THEN Fmarks ELSE NULL),
EC4 = SUM(CASE WHEN FSubject = 'EC4' THEN Fmarks ELSE NULL),
EC5 = SUM(CASE WHEN FSubject = 'EC5' THEN Fmarks ELSE NULL)
FROM Student
GROUP BY fStudentname
) AS Src
JOIN's
solution:
DECLARE @student TABLE(stuID INT, fStudentname VARCHAR(30), FSubject VARCHAR(30), Fmarks INT)
INSERT INTO @student (stuID, fStudentname, FSubject, Fmarks)
VALUES(1, 'Savita', 'EC1', 30),
(1, 'Savita', 'EC2', 19),
(1, 'Savita', 'EC3', 28),
(1, 'Savita', 'EC4', 30),
(1, 'Savita', 'EC5', 60),
(2, 'Vidya', 'EC1', 90),
(2, 'Vidya', 'EC2', 80),
(2, 'Vidya', 'EC3', 85),
(2, 'Vidya', 'EC4', 75),
(2, 'Vidya', 'EC5', 99),
(3, 'Tanesh', 'EC1', 75),
(3, 'Tanesh', 'EC2', 80),
(3, 'Tanesh', 'EC3', 85),
(3, 'Tanesh', 'EC4', 28),
(3, 'Tanesh', 'EC5', 86)
SELECT fStudentname as Student_name, EC1 ,EC2,EC3,EC4,EC5,Total =([EC1]+[EC2]+[EC3]+[EC4]+[EC5])
FROM (
SELECT A.fStudentname, A.EC1, B.EC2, C.EC3, D.EC4, E.EC5
FROM (
(SELECT fStudentname, SUM(Fmarks) AS EC1 FROM @student WHERE FSubject = 'EC1' GROUP BY fStudentname) AS A
INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC2 FROM @student WHERE FSubject = 'EC2' GROUP BY fStudentname) AS B ON A.fStudentname = B.fStudentname
INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC3 FROM @student WHERE FSubject = 'EC3' GROUP BY fStudentname) AS C ON A.fStudentname = C.fStudentname
INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC4 FROM @student WHERE FSubject = 'EC4' GROUP BY fStudentname) AS D ON A.fStudentname = D.fStudentname
INNER JOIN (SELECT fStudentname, SUM(Fmarks) AS EC5 FROM @student WHERE FSubject = 'EC5' GROUP BY fStudentname) AS E ON A.fStudentname = E.fStudentname
)
) AS Final
Note: tested on temporary table. You have to replace
@student
with real table name.