Oracle row and column career change ____oracle

First, row to column

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

Example

1--Student Score table 2 CREATE TABLE grade (3 ID varchar2 ( theNot NULL, 4 name VARCHAR2 ( -Not NULL, 5 course VARCHAR2 ( -Not NULL, 6 score number (3Not NULL, 7 primary KEY (ID) 8) 9 10--initialization data insert into grade values (' 100001 ', ' John ', ' language ', A); Insert into grade values (' 100002 ', ' John ', ' Math ', -); Insert into grade values (' 100003 ', ' John ', ' English ', -); Insert into grade values (' 100004 ', ' Dick ', ' language ', the); Insert into grade values (' 100005 ', ' Dick ', ' math ', A); Insert into grade values (' 100006 ', ' Dick ', ' English ',Bayi); Insert into grade values (' 100007 ', ' Harry ', ' language ', He); Insert into grade values (' 100008 ', ' Harry ', ' mathematics ', the); Insert into grade values (' 100009 ', ' Harry ', ' English ', the); Insert into grade values (' 100010 ', ' Zhao Liu ', ' language ', -); Insert into grade values (' 100011 ', ' Zhao Liu ', ' math ', the); Insert into grade values (' 100012 ', ' Zhao Liu ', ' English ', A); Insert into grade values (' 100013 ', ' Li Ming ', ' Chinese ', the); Insert into grade values (' 100014 ', ' Li Ming ', ' math ', A); Insert into grade values (' 100015 ', ' Li Ming ', ' English ', -);

Row-Column statement

1 Select
2 t.name "name",
0) "language",
0) "mathematics", 
0) "English"   
6 from grade T 
7 GROUP BY T.name

Because the group by T.name is used here, you can only use aggregate functions (sum,max,min) for the presentation of score, and you will have an error if you do not use aggregate functions.

The results of the implementation are as follows:

　　

Second, the column to change careers

In contrast to row-and-column, some scenarios just need this action.

Examples are as follows:

1--Column Careers
 2 CREATE TABLE Sc_grade (
 3     ID varchar2 () not NULL,
 4     name varchar2 Not NULL,
 5     cn_score Number (3) is not NULL,
 6     math_score Number (3) is not NULL,
 7   en_score Number (3) NOT NULL,
 8     primary key (ID)
 9)  
11--Initial data
 A  - );
 He  the );
 -  the );
 the  A Bayi);
 the  A );

Biography Line statement

1 Select name "Name", ' Language ' course, Cn_score as score from Sc_grade
2 Union 
3 Select name ' name ', ' Math ' course, math_s Core as score from Sc_grade
4 Union 
5 Select name "Name", ' English ' course, En_score as score from Sc_grade

The results are as follows:

　　

You can also use Insert all. into complete. Use Insert All ... into needs to create a new table to hold the converted data.

The table structure is as follows:

1--results are stored
in table 2 CREATE TABLE Test_sc_grade (  
3     namevarchar2 () not NULL,   
4     course VARCHAR2 ( NOT NULL,   
5 score number     (3) is not null 
6)    

Execute the statement as follows:

1 Insert all 
2 into Test_sc_grade (Name,course,score) values (name, ' language ', Cn_score) 
3 into Test_sc_grade (name, Course,score) VALUES (name, ' math ', Math_score) 
4 into Test_sc_grade (Name,course,score) values (name, ' English ', En_score) C3/>5 select Name, Cn_score, Math_score, En_score from Sc_grade; 
6 commit; 
7 SELECT * FROM Test_sc_grade

The result query is consistent with the screenshot above.