学生按科目比较报告 [英] Comparision Report of Students By Subjects

问题描述

您好专家，

我有一张桌子，供学生记录学科知识.我如何才能明智地获得记录学生的知识和学科知识.

例如:
Subject1 Subject2 ................ N
学生1分
学生2
.
.
.
N

Hello Experts,

I have a tables for students records of subject wise. How can i get the record student wise and subject wise.

Ex:
Subject1 Subject2 ................N
Student1 Marks
Student2
.
.
.
N

There Are Three Tables :

1 : Student : That have  Student Id and Student Name
2 : Subject : That Have Subject Id and Subject Name
3 : Student_Records : That have Student Id and Subject ID and Marks.


Now i want to generate the comparison record for all students and all subjects. Report will be in given format.

 <pre>
          Math         English     Art         Music   -----------------N
Student1   10             20        30           40
Student2   20            11         22           01
Student3   3             4           6            7
-
-
-
-
N</pre>

谢谢

Thanks

推荐答案

我认为这对您有帮助

I think this will help you

DECLARE @StudentInfo AS TABLE
(
 Student NVARCHAR(50),
 Subject1 BIGINT ,
 Subject2 BIGINT ,
 Subject3 BIGINT ,
 Subject4 BIGINT 
)


INSERT INTO @StudentInfo 
SELECT 'Student1',54,42,50,62
Union
SELECT 'Student2',38,40,45,78



SELECT Student, Subject, Marks
FROM 
   (SELECT Student, Subject1, Subject2, Subject3, Subject4
   FROM @StudentInfo) p
UNPIVOT
   (Marks FOR Subject IN 
      (Subject1, Subject2, Subject3, Subject4)
)AS unpvt;

这篇关于学生按科目比较报告的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！