Oracle row-to-column and column-to-row

Oracle row-to-column and column-to-row
I. Row-to-column Conversion
The following format must be set:

Convert:

This is the most common row-to-column conversion. The main principle is to use the decode function, aggregate function (sum), and group by grouping.
[SQL]View plaincopy
Create table test (id varchar2 (255) primary key not null, name varchar2 (255), course varchar2 (255), score varchar2 (255 )); insert into test values (sys_guid (), 'hangsan ', 'China', 85); insert into test values (sys_guid (), 'hangsan', 'mat', 78 ); insert into test values (sys_guid (), 'hangsan ', 'English', 90); insert into test values (sys_guid (), 'lisi', 'China', 73 ); insert into test values (sys_guid (), 'lisi', 'mat', 84); insert into test values (sys_guid (), 'lisi', 'English ', 92 );[SQL]View plaincopyselect t. name, sum (decode (t. course, 'China', score, null) as chinese, sum (decode (t. course, 'mat', score, null) as math, sum (decode (t. course, 'English ', score, null) as englishfrom test t group by t. nameorder by t. name[SQL]View plaincopycreate table test (id varchar2 (255) primary key not null, name varchar2 (255), ch_score varchar2 (255), math_score varchar2 (255), en_score varchar2 (255 )); insert into test values (sys_guid (), 'hangsan ', 90); insert into test values (sys_guid (), 'lisi', 82 );[SQL]View plaincopyselect name, 'China' COURSE, ch_score as SCORE from test union select name, 'mates' COURSE, MATH_SCORE as SCORE from testunion select name, 'English 'COURSE, EN_SCORE as SCORE from test order by name, COURSE