Блог

Данные:

 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;  

Запрос:

 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;  

Учащийся выделяется как недопустимое имя столбца, и когда я удаляю Учащегося из запроса и выполняю его, это вывод:

Я пытался каким-либо образом вводить и выводить столбец «Студент» в запросе, но он выдает ошибку. Я хочу, чтобы результат состоял в том, чтобы отобразить предметы в столбцах и отобразить вычисленную среднюю оценку учащихся за два семестра. Подобный этому:

Укажите имя учащегося в подзапросе
ИЗ ( ВЫБЕРИТЕ Учащегося, Оценки, Тему ИЗ отчета О результатах )В КАЧЕСТВЕ исходного

 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;