Oracle row-to-column and column-changing careers

First, row to column
1.1. Initial test data

Table structure: Test_tb_grade

SQL code:

1 CREATE TABLE Test_tb_grade
2 (
3 ID Number (TEN) is not NULL,
4 user_name VARCHAR2 (CHAR),
5 COURSE VARCHAR2 (CHAR),
6 score FLOAT
7)

Initial data such as:

 
1.2, if you need to implement the following query:

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, the specific SQL is as follows:

SQL code:
1    select t.user_name,
2      sum (decode (t.course, ' language ', score,null)) as Chinese,
3      sum (decode (t.course, ' math ', score,null) as MATH,
4      SUM (Decode (t.course, ' English ', score,null)) as 中文版
5    from test_tb_grade t
6     GROUP BY T.user_name
7    order by T.user_name
1.3, extension

If you want to achieve a different score segment for each subject, as follows:
 

 

The specific implementation of SQL is as follows:
SQL code:

T2 Select. SCORE_GP,
The sum of decode (t2.course, ' language ', countnum,null) as Chinese,
SUM (Decode (t2.course, ' math ', countnum,null) as MATH,
SUM (Decode (t2.course, ' English ', countnum,null)) as 中文版
From (
Select T.course,
When the case was T.score <60 then ' 00-60 '
T.score >=60 and T.score <80 then ' 60-80 '
T.score >=80 Then ' 80-100 ' End as SCORE_GP,
Ten count (T.score) as Countnum
From Test_tb_grade t
Group BY T.course, 
When T.score <60 then ' 00-60 '
T.score >=60 and T.score <80 then ' 60-80 '
T.score >=80 Then ' 80-100 ' end
ORDER by T.course) T2
GROUP by T2. Score_gp
ORDER by T2. Score_gp
Second, the list of career change

1.1. Initial test data
Table structure: Test_tb_grade2 
SQL code:
1 CREATE TABLE Test_tb_grade2
2 (
3 ID Number (TEN) is not NULL,
4 user_name VARCHAR2 (CHAR),
5 Cn_score FLOAT,
6 Math_score FLOAT,
7 En_score FLOAT
8)

Initial data such as:

1.2, if you need to implement the following query:

This is the most common column change, the main principle is to use the union inside the SQL, the specific SQL statements are as follows:
SQL code: 

1 Select user_name, ' Cn_score ' COURSE, Cn_score as score from Test_tb_grade2 
2 Union
3 Select user_name, ' Math_score ' COURSE, Math_score as score from Test_tb_grade2 
4 Union
5 Select user_name, ' En_score ' COURSE, En_score as score from Test_tb_grade2 
6 ORDER BY User_name,course

You can also make use of the insert all into ... Select to first build a table test_tb_grade3:
SQL code:
1 CREATE TABLE Test_tb_grade3
2 ( 
3 user_name VARCHAR2 (CHAR),
4 COURSE VARCHAR2 (CHAR),
5 Score FLOAT
6)

Then execute the following sql: 
SQL code:
1 Insert All
2 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' languages ', Cn_score)
3 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' math ', Math_score)
4 into Test_tb_grade3 (User_name,course,score) VALUES (user_name, ' English ', En_score)
5 Select user_name, Cn_score, Math_score, en_score from Test_tb_grade2;
6 commit;

Do not forget the commit operation, and then query the TEST_TB_GRADE3, found that the table data is the column to go.

Oracle row-to-column and column-changing careers