Add a calculated column to pivot table columns in SQL Server

user_15159278 0
Viewed 172

I need to add an extra column that can shows AVG(Marks) foreach row using pivot table if it's possible in sql server.

1-Create Grades Table

  CREATE TABLE Grades(
  [Student] VARCHAR(50),
  [Subject] VARCHAR(50),
  [Marks]   INT)

2-Inserting some values

INSERT INTO Grades VALUES 
('Jacob','Mathematics',100),
('Jacob','Science',95),
('Jacob','Geography',90),
('Amilee','Mathematics',90),
('Amilee','Science',90),
('Amilee','Geography',100)

3-Showing results

  SELECT * FROM (SELECT[Student],[Subject],[Marks]FROM Grades) StudentResults
  PIVOT (
  MAX([Marks])
  FOR [Subject]
  IN ([Mathematics],[Science],[Geography])) AS PivotTable

I need to add column like in this image:

enter image description here

sql-server
1 Answers

You can use the window function avg() over() in the select which "feeds" your pivot

Example

 SELECT * 
  FROM (SELECT[Student]
             ,[Subject]
             ,[Marks]
             ,[Avg(Marks)] = avg([Marks]) over (partition By [Student])
         FROM Grades) StudentResults
 PIVOT (  MAX([Marks]) FOR [Subject] IN ([Mathematics],[Science],[Geography])) AS PivotTable
user_15700000
Related