Oracle row-to-column and column-to-row, oracle column-to-column Conversion

Oracle row-to-column and column-to-row, oracle column-to-column Conversion

I. Row-to-column Conversion

In some application scenarios, such as student scores. The scores of all students are saved in a table. When we need to display the scores of each student subject in the form of a list, we need to use row-to-column.

Example

1 -- Student discovery table 2 create table grade (3 id varchar2 (64) not null, 4 name varchar2 (20) not null, 5 course varchar2 (100) not null, 6 score number (3) not null, 7 primary key (id) 8) 9 10 -- initialize Data 11 insert into grade values ('20140901', 'zhang san', 'China ', 75); 12 insert into grade values ('123', 'zhang san', 'mat', 80); 13 insert into grade values ('123', 'zhang san ', 'English ', 80); 14 15 insert into grade values ('123456', 'lily', 'China', 86); 16 insert into grade values ('123456 ', 'Li si', 'mat', 66); 17 insert into grade values ('123456', 'Li si', 'English ', 81 ); 18 19 insert into grade values ('20140901', 'wang wu', 'China', 92); 20 insert into grade values ('20160901', 'wang wu', 'mat ', 78); 21 insert into grade values ('20140901', '王', 'English ', 82); 22 23 insert into grade values ('20140901', 'zhao liu ', 'China', 80); 24 insert into grade values ('123', 'zhao liu', 'mat', 90); 25 insert into grade values ('123 ', 'zhao liu', 'English ', 77); 26 27 insert into grade values ('123', 'Li Ming', 'China', 69 ); 28 insert into grade values ('20140901', 'lilim', 'mat', 75); 29 insert into grade values ('20140901', 'lilim', 'inc ', 80 );

Row-to-column statements

1 select2 t. name "name", 3 sum (decode (t. course, 'China', t. score, 0) "language", 4 sum (decode (t. course, 'mat', t. score, 0) "Mathematics", 5 sum (decode (t. course, 'English ', t. score, 0) "English" 6 from grade t 7 group by t. name

Because group by t. name is used here, you can only use aggregate functions (sum, max, min) to display scores. If you do not use aggregate functions, an error is returned.

The execution result is as follows:

　　

Ii. Column-to-row

Unlike row-to-column conversion, this operation is required in some scenarios.

Example:

1 -- create table SC _grade (3 id varchar2 (64) not null, 4 name varchar2 (20) not null, 5 cn_score number (3) not null, 6 math_score number (3) not null, 7 en_score number (3) not null, 8 primary key (id) 9) 10 11 -- Initial Data 12 insert into SC _grade values ('123456', 'zhang san', 75, 80, 80); 13 insert into SC _grade values ('123456', 'wang wu ', 92, 78, 82); 14 insert into SC _grade values ('20140901', 'zhao liu', 80, 90, 77); 15 insert into SC _grade values ('20140901 ', 'Li si', 86, 66, 81); 16 insert into SC _grade values ('123', 'Li Ming ', 69, 75, 80 );

Column-based row statement

1 select name "name", 'China' course, cn_score as score from SC _grade2 union 3 select name "name", 'mat' course, math_score as score from SC _grade4 union 5 select name ", 'English 'course, en_score as score from SC _grade6 order by" name ", course

The result is as follows:

　　

You can also use insert all... into to complete the operation. Insert all... Into needs to create a new table to save the converted data.

The table structure is as follows:

1 -- result storage table 2 create table test_ SC _grade (3 name varchar2 (20) not null, 4 course varchar2 (100) not null, 5 score number (3) not null 6)

The execution statement is as follows:

1 insert all 2 into test_ SC _grade (name, course, score) values (name, 'China', cn_score) 3 into test_ SC _grade (name, course, score) values (name, 'math_score) 4 into test_ SC _grade (name, course, score) values (name, 'English ', en_score) 5 select name, cn_score, math_score, en_score from SC _grade; 6 commit; 7 select * from test_ SC _grade

The query result is consistent with the preceding one.