SQL query with PIVOT-CodePudding

The data:

CREATE TABLE [dbo].[Grade_Report](
    [Student] [varchar](50) NULL,
    [Subject] [varchar](50) NULL,
    [Grades] [numeric](18, 2) NULL,
    [Semester] [int] NULL
);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Mathematics', CAST(82.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Science', CAST(83.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Programming', CAST(91.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'History', CAST(89.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'English', CAST(95.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Mathematics', CAST(79.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Science', CAST(80.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'Programming', CAST(92.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'History', CAST(95.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jamie', N'English', CAST(93.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Mathematics', CAST(95.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Science', CAST(95.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Programming', CAST(90.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'History', CAST(92.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'English', CAST(96.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Mathematics', CAST(78.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Science', CAST(79.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'Programming', CAST(82.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'History', CAST(80.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jenny', N'English', CAST(79.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Mathematics', CAST(96.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Science', CAST(92.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Programming', CAST(93.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'History', CAST(88.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'English', CAST(94.00 AS Numeric(18, 2)), 1);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Mathematics', CAST(90.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Science', CAST(92.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'Programming', CAST(98.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'History', CAST(93.00 AS Numeric(18, 2)), 2);
INSERT [dbo].[Grade_Report] ([Student], [Subject], [Grades], [Semester]) VALUES (N'Jerome', N'English', CAST(90.00 AS Numeric(18, 2)), 2);


SELECT * FROM Grade_Report;

The query:

SELECT Student, [English], [Mathematics], [Science], [Programming], [History]
FROM
(
    SELECT Grades, Subject FROM Grade_Report
)AS SourceTable
PIVOT
(
    AVG(Grades)
    FOR Subject IN([English], [Mathematics], [Science], [Programming], [History])
)AS PivotTable;

The Student is highlighted as invalid column name, and when I remove the Student from the query and execute this is the output:

I tried putting in and out the student column in any way in the query but it gives error. What I want the output would be is to display the subjects into columns and will the display the computed average grade of the students for two semesters. Like this:

CodePudding user response：

Put the name of the student in the subquery
FROM ( SELECT Student, Grades, Subject FROM Grade_Report )AS SourceTable

SELECT Student, [English], [Mathematics], [Science], [Programming], 
[History]
FROM
(
    SELECT Student, Grades, Subject FROM Grade_Report
)AS SourceTable
PIVOT
(
    AVG(Grades)
    FOR Subject IN([English], [Mathematics], [Science], [Programming], 
    [History])
)AS PivotTable;