SQL query row-to-Column

-- SQL interview questions

/* Problem: Assume that there is a student renewal table (tb) as follows:

Name course score

Zhang San Language 74

James math 83

Zhang San physical 93

Li Si language 74

Li Si mathematics 84

Li Si physical 94

(The following result is displayed ):

Name, Chinese, Mathematics, Physics

----------------

Li Si 74 84 94

Zhang San 74 83 93

-------------------*/

 

Create table tb (name varchar (10), course varchar (10), score int) insert into tb values ('zhang san', 'China', 74) insert into tb values ('zhang san', 'mat', 83) insert into tb values ('zhang san', 'Physical ', 93) insert into tb values ('Li si ', 'China', 74) insert into tb values ('lily', 'mat', 84) insert into tb values ('lily', 'Physical ', 94) go -- SQL server 2000 static SQL indicates that the course only includes three courses: Chinese, mathematics, and physics. (The same as below) select name as name, max (case course when 'chine' then score else 0 end) language, max (case course when 'mate' then score else 0 end) mathematics, max (case course when 'physical 'then score else 0 end) Physical from tbgroup by name go -- SQL server 2000 dynamic SQL, it refers to more than three courses: Chinese, mathematics, and physics. (The same as below) declare @ SQL varchar (8000) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from tb) as aset @ SQL = @ SQL + 'from tb group by name' exec (@ SQL) go -- SQL server 2005 static SQL. Select * from (select * from tb) a between (max (score) for course in (language, mathematics, physics) bgo -- SQL server 2005 dynamic SQL. Declare @ SQL varchar (8000) select @ SQL = isnull (@ SQL + '], [', '') + course from tb group by course set @ SQL = '[' + @ SQL + ']' exec ('select * from (select * from tb) a round (max (score) for course in ('+ @ SQL +') B ') go

 

---------------------------------

/*
Problem: Based on the above results, the average score and total score are added. The following result is obtained:
Name, Chinese, mathematics, and physics average score
--------------------------
Li Si 74 84 94 84.00 252
Zhang San 74 83 93 83.33 250
*/