Oracle converts the score row into a column, and oracle scores

Oracle converts the score row into a column, and oracle scores

1. The table contains the following data:

Name	Subject	January	February	March
Zhang San	Chinese	30	40	50
Zhang San	Mathematics	56	65	78
Zhang San	English	28	86	48
Li Si	Chinese	31	41	51
Li Si	Mathematics	57	66	79
Li Si	English	29	87	49

Convert

Name	October	October	October	January mathematics	February mathematics	March mathematics	January English	February English	March English
Li Si	31	41	51	57	66	79	29	87	49
Zhang San	30	40	50	56	65	78	28	86	48

2. Create Test Data

Create table GRADE_TABLE (STU_NAME VARCHAR (20), subject varchar (20), MONTH1 int default 0, -- January mon2int DEFAULT 0, -- February MONTH3 int default 0 ); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('zhang san', 'China', '30', '40', '50 '); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('zhang san', 'mat', '56', '65', '78 '); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('zhang san', 'English ', '28', '86', '48 '); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('Li si', 'China', '31', '41', '51 '); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('Li si', 'mat', '57 ', '66', '79 '); insert into GRADE_TABLE (STU_NAME, SUBJECT, MONTH1, mon22. MONTH3) values ('Li si', 'English ', '29', '87', '49 ');

3. Process SQL

Select stu_name, sum (January language) January language, sum (February language) February language, sum (March language) March language, sum (January mathematics) January mathematics, sum (February mathematics) february mathematics, sum (March mathematics) March mathematics, sum (January English) January English, sum (February English) February English, sum (March English) March English from (select stu_name, sum (case subject when 'then month1 end) as October language, sum (case subject when' 'then mon2end) as October language, sum (case subject when 'then month3 end) as October language, sum (case subject when 'mate' then month1 end) as October mathematics, sum (case subject when 'mate' then mon2end) as February mathematics, sum (case subject when 'mate' then month3 end) as March mathematics, sum (case subject when 'then month1 end) as January English, sum (case subject when' 'then mon2end) as February English, sum (case subject when 'then month3 end ') as March English from GRADE_TABLE group by stu_name, subject) group by stu_name -- danielinbiti