First, row to column
You need to use the following format, for example
Convert to:
This is the most common row-to-column, the main principle is the use of the Decode function, aggregation function (sum). Combined with the group by group implementation
[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 (),' Zhangsan ',' Language ', 85);
- Insert into test values(Sys_guid (),' Zhangsan ', ' mathematics ', 78);
- Insert into test values(Sys_guid (),' Zhangsan ',' English ' , 90);
- Insert into test values(Sys_guid (),' Lisi ', ' Chinese ' , 73);
- Insert into test values(Sys_guid (),' Lisi ', ' Math ' , 84);
- Insert into test values(Sys_guid (),' Lisi ',' English ' , 92);
The row-to-column SQL statement is:
[SQL]View Plaincopy
- Select T. name ,
- sum (Decode (t.course, ' language ', score,null)) as Chinese,
- sum (Decode (t.course, ' math ', score,null)) as Math,
- sum (Decode (t.course, ' English ', score,null)) as 中文版
- from Test T
- Group by T. name
- Order by T. name
Second, the list of career change
Will be for example the following format
Converted to
This is the most common column-changing career. The main principle is to use the union inside the SQL
[SQL]View Plaincopy
- Create 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 (),' Zhangsan ', 88,76,90);
- Insert into test values(Sys_guid (),' Lisi ', 91,67,82);
The column career SQL statement is:
[SQL]View Plaincopy
- Select name, ' language ' COURSE, ch_score as score from Test
- Union Select name, ' math ' COURSE, math_score as score from Test
- Union Select name, ' English ' COURSE, En_score as score from Test
- Order by name , COURSE
It Ninja Turtles Oracle Row-to-column, column-changing career