In the table has a name, subject fields, how to calculate average scores, output and the performance of the table?

CodePudding user response:

 if OBJECT_ID (' tempdb for.. # test ') is not null drop table test 
The create table # test (name nvarchar (10), subjects nvarchar (10), grade a decimal (4, 0)) 
Insert into # test (name, subjects and grades) 
Select 'zhang', 'mathematics', 56 union all 
Select 'zhang', 'English', 57 union all 
Select 'zhang', 'Chinese' 59 union all 
Select 'bill', 'mathematics', 60 union all 
Select the 'bill', 'English', 61 union all 
Select 'bill', 'language', 62 union all 
Select 'bill', 'self-study, 63 

Select name, CAST (AVG (performance) as a decimal (4, 2)) [score] # from the test group by name 
/* 
Li si 61.50 
Zhang SAN 57.33 */
Select * from # test 
The pivot 
(
Max (grades) 
For subjects (in math, English, Chinese, self-study) - step 2 
#) as the test 
/* 
Li si, 60, 61, 62, 63 
Zhang SAN 56 57 59 NULL */

CodePudding user response:

reference 1st floor XXXXn handsome response:

 if OBJECT_ID (' tempdb for.. # test ') is not null drop table test 
The create table # test (name nvarchar (10), subjects nvarchar (10), grade a decimal (4, 0)) 
Insert into # test (name, subjects and grades) 
Select 'zhang', 'mathematics', 56 union all 
Select 'zhang', 'English', 57 union all 
Select 'zhang', 'Chinese' 59 union all 
Select 'bill', 'mathematics', 60 union all 
Select the 'bill', 'English', 61 union all 
Select 'bill', 'language', 62 union all 
Select 'bill', 'self-study, 63 

Select name, CAST (AVG (performance) as a decimal (4, 2)) [score] # from the test group by name 
/* 
Li si 61.50 
Zhang SAN 57.33 */
Select * from # test 
The pivot 
(
Max (grades) 
For subjects (in math, English, Chinese, self-study) - step 2 
#) as the test 
/* 
Li si, 60, 61, 62, 63 
Zhang SAN 56 57 59 NULL */

thank you