I created a table like 
converted row values into columns by using pivot
Select
StudentName, EC1, EC2, EC3, EC4, EC5
from
tblStudent
Pivot
(Sum(Marks) for subject in ([EC1], [EC2], [EC3], [EC4], [EC5])
) as PivotTable
Now I want to add a new column after EC5 with name Toatl values will be sum of all 5 subjects
I tried using temp table
CREATE TABLE #StudentTemp
(
StudentName varchar(20),
EC1 int,EC2 int,EC3 int,EC4 int,EC5 int
)
GO
INSERT INTO #StudentTemp
SELECT StudentName, EC1, EC2, EC3, EC4, EC5
FROM tblStudent
PIVOT
(SUM(Marks) for subject in ([EC1], [EC2], [EC3], [EC4], [EC5])
) as PivotTable
SELECT
*,
SUM(EC1 + EC2 + EC3 + EC4 + EC5) AS Total
FROM
#StudentTemp
GROUP BY
StudentName
After this I tried to join
SELECT *
FROM #StudentTemp sp
INNER JOIN tblStudent s ON sp.StudentName = s.StudentName
GROUP BY s.StudentName
SELECT StudentName, SUM(EC1 + EC2 + EC3 + EC4 + EC5) AS Total
FROM #StudentTemp
GROUP BY StudentName
SELECT SUM(EC1 + EC2),*
FROM #StudentTemp
GROUP BY StudentName
