It Ninja Turtles Oracle Row-to-column, column-changing career

First, row to column

The following format is required

Convert to:

This is the most common row-to-column, the main principle is to use the Decode function, aggregation function (sum), combined with the group by group implementation of the

[SQL]View Plaincopy

2. Create Table Test (
4. ID VARCHAR2 (255) primary key not null,
6. name varchar2 (255),
8. Course VARCHAR2 (255),
10. Score VARCHAR2 (255)
12. );
14. Insert into test values(Sys_guid (),' Zhangsan ', ' Chinese '  , 85);
16. Insert into test values(Sys_guid (),' Zhangsan ',' math '   , 78);
18. Insert into test values(Sys_guid (),' Zhangsan ',   ' English ', 90);
20. Insert into test values(Sys_guid (),' Lisi ', ' Chinese '  , 73);
22. Insert into test values(Sys_guid (),' Lisi ',' math '  , 84);
24. Insert into test values(Sys_guid (),' Lisi ', ' English '  , 92);

The row-to-column SQL statement is:

[SQL]View Plaincopy

2. Select T. name ,   
4. sum (Decode (t.course, ' language ', score,null)) as Chinese,
6. sum (Decode (t.course, ' math ', score,null)) as Math,
8. sum (Decode (t.course, ' English ', score,null)) as 中文版
10. from Test T
12. Group by T. name    
14. Order by T. name    

Second, the list of career change

The following format

Converted to

This is the most common column change, the main principle is to use the union inside the SQL

[SQL]View Plaincopy

2. Create Table Test (
4. ID VARCHAR2 (255) primary key not null,
6. name varchar2 (255),
8. Ch_score varchar2 (255),
10. Math_score varchar2 (255),
12. En_score VARCHAR2 (255)
14. );
18. Insert   into test values(Sys_guid (),' Zhangsan ', 88,76,90);
20. Insert into test values(Sys_guid (),' Lisi ',  91,67,82);

The column career SQL statement is:

[SQL]View Plaincopy

2. Select name, ' language ' COURSE, ch_score as score from Test
4. Union Select name, ' math ' COURSE, Math_score as score from Test
6. Union  Select name, ' English ' COURSE, en_score as Score from test
8. Order by name , COURSE
   

It Ninja Turtles Oracle Row-to-column, column-changing career