Summary of Oracle row-column Conversion

The directory structure is as follows:
Row-to-Column
Column-to-row
[1]. Row-to-column Conversion
 
1.1 initial test data
 
Table Structure: TEST_TB_GRADE
 
SQL code
Create table TEST_TB_GRADE
(
Id number (10) not null,
USER_NAME VARCHAR2 (20 CHAR ),
COURSE VARCHAR2 (20 CHAR ),
SCORE FLOAT
)
The initial data is as follows:

 
1.2 If you need to implement the following query:

 
This is the most common row-to-column conversion. The main principle is to use the decode function, aggregate function (sum), and group by grouping. The specific SQL statement is as follows:
SQL code
Select t. user_name,
Sum (decode (t. course, 'China', score, null) as CHINESE,
Sum (decode (t. course, 'mate', score, null) as MATH,
Sum (decode (t. course, 'English ', score, null) as ENGLISH
From test_tb_grade t
Group by t. user_name
Order by t. user_name
 
 
1.3 Extension
 
If you want to calculate the different score segments of each course, as follows:

 
The specific implementation SQL is as follows:
SQL code
Select t2.SCORE _ GP,
Sum (decode (t2.course, 'China', COUNTNUM, null) as CHINESE,
Sum (decode (t2.course, 'mate', COUNTNUM, null) as MATH,
Sum (decode (t2.course, 'English ', COUNTNUM, null) as ENGLISH
From (
Select t. course,
Case when t. score <60 then '00-60'
When t. score> = 60 and t. score <80 then '60-80'
When t. score> = 80 then '80-100 'end as SCORE_GP,
Count (t. score) as COUNTNUM
FROM test_tb_grade t
Group by t. course,
Case when t. score <60 then '00-60'
When t. score> = 60 and t. score <80 then '60-80'
When t. score> = 80 then '80-100 'end
Order by t. course) t2
Group by t2.SCORE _ GP
Order by t2.SCORE _ GP
 
[2] column-to-row
 
1.1 initial test data
Table Structure: TEST_TB_GRADE2
SQL code
Create table TEST_TB_GRADE2
(
Id number (10) not null,
USER_NAME VARCHAR2 (20 CHAR ),
CN_SCORE FLOAT,
MATH_SCORE FLOAT,
EN_SCORE FLOAT
)
 
The initial data is as follows:

 
1.2 If you need to implement the following query:

 
This is the most common column-to-row mechanism. The main principle is to use union in SQL. The specific SQL statement is as follows:
SQL code
Select user_name, 'China' COURSE, CN_SCORE as SCORE from test_tb_grade2
Union select user_name, 'mate' COURSE, MATH_SCORE as SCORE from test_tb_grade2
Union select user_name, 'English 'COURSE, EN_SCORE as SCORE from test_tb_grade2
Order by user_name, COURSE
 
You can also use [insert all into... select] to create a table TEST_TB_GRADE3:
SQL code
Create table TEST_TB_GRADE3
(
USER_NAME VARCHAR2 (20 CHAR ),
COURSE VARCHAR2 (20 CHAR ),
SCORE FLOAT
)
Run the following SQL statement:
 
SQL code
Insert all
Into test_tb_grade3 (USER_NAME, COURSE, SCORE) values (user_name, 'China', CN_SCORE)
Into test_tb_grade3 (USER_NAME, COURSE, SCORE) values (user_name, 'mat', MATH_SCORE)
Into test_tb_grade3 (USER_NAME, COURSE, SCORE) values (user_name, 'English ', EN_SCORE)
Select user_name, CN_SCORE, MATH_SCORE, EN_SCORE from test_tb_grade2;
Commit;
Do not forget the commit operation, and then query TEST_TB_GRADE3. The data in the table is converted into rows by column.