Recently, due to the development needs, it involved the grade statistics, which is designed to the application of the SQL crosstab, tried to try, I feel very thoughtful, now write it out, there is a welcome welcome point ^ _ ^ First we build The following table: SC (transcript) stuid clsid scroe ------------------------------------- ---------------------------- 0101 1 75.00102 1 70.00103 1 90.00101 2 89.00102 2 80.00103 2 99.00101 3 89.00102 3 79.00103 3 67.0 where stuid is expressed Student number, clsid representation course number, scroe expresses grades stu (Student Table) stuid stuname ------------------------------------------------------------- ---------------------------- 101 Three 102 Li 4th 103 Wang Wu CLS (Course) CLSID NAME ----------------------------------- ------------------------- 1 Language 2 Mathematics 3 English next is the key, in fact, it is not a good complicated, it is used to use dynamic sqldeclare @ SQL NVARCHAR (4000), @ sql1 nvarchar (4000) SELECT @SQL = ', @ SQL1 =' 'SELECT @ SQL = @ SQL ', [' Name '] = SUM (Case CLSID WHEN '' CLSID ' '' Ten scroe else 0 end) ', @ SQL1 = @ SQL1 ', [' Name ' Name] = (Select Sum (1) from # where [' Name
']> = a. [SELECT DISTINCT B.CLSID, C.NAME From SC AS B INNER JOIN CLS AS C on C. Clsid = B.CLSID) AS A Order By Clsidexec (' Select Stuid Learn ' @ SQL ', Total Score = Sum (Scroe), Average = Convert (Dec (5, 1), AVG (Scroe), Total Name = (Select Sum (Select Sum (SELECT STUID, AA = SUM (SCROE) from SC Group By Stuid) AA where sum (a.scroe) <= aa) INTO # from sc as a group by stuid select b.stuname as name, a. * ' @ SQL1 ' from # # # As a inner join stu as b on a. Learn = b.stuid ') The following is the result: Names Language Mathematics English Total Average Points Total Total Language Name Number Multi --------------------------- -------------------------------------------------- ------------------------------ Zhang San 0101 75.0 89.0 89.0 253.0 84.3 2 2 2 1 Li Si 0102 70.0 80.0 79.0 229.0 76.3 3 3 3 2 Wang 5103 90.0 99.0 67.0 256.0 85.3 1 1 1 3 This is the usage of the crosstab. For the statistics of the report, we can write a more powerful query according to this class.
