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 the different scores of each course of work statistics, 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.
Source: >  

From for notes (Wiz)

Oracle row-to-column and column-changing careers