PL/SQL converts rows and columns

PL/SQL converts rows and columns. In this blog post, we write a simple conversion of rows and columns. Here is a specific example. Some people have asked related questions on the Forum before. Many of the answers mentioned above are feasible. Of course, they think that they are very high-end, but they have no answer. Next, go to the topic. Www.2cto.com part1: column-to-row. Requirement: The following table is provided in the example:

The table definition is as follows: create table TEST_TB_GRADE (id number (10) not null, USER_NAME VARCHAR2 (20 CHAR), COURSE VARCHAR2 (20 CHAR), score float) requires conversion:

The following is a feasible PL/SQL statement: www.2cto.com select t. user_name as name, sum (decode (t. course, 'China', score, null) as language, sum (decode (t. course, 'mat', score, null) as mathematics, sum (decode (t. course, 'English ', score, null) as English from test_tb_grade t group by t. user_name order by t. the implementation result of user_name is shown in the requirement.

Part2: Convert rows to columns. Requirement: The following table is provided in the example.

The table structure is as follows: create table TEST_TB_GRADE2 (id number (10) not null, USER_NAME VARCHAR2 (20 CHAR), CN_SCORE FLOAT, MATH_SCORE FLOAT, EN_SCORE FLOAT) needs to be converted to the following form: www.2cto.com

The following is an executable PL/SQL statement: select user_name, 'China' as COURSE, CN_SCORE as SCORE from test_tb_grade2 union allselect user_name, 'mate' as COURSE, MATH_SCORE as SCORE from test_tb_grade2 union allselect user_name, 'English 'as COURSE, EN_SCORE as SCORE from test_tb_grade2 order by user_name. The COURSE implementation result is shown in the requirement.

Author: DebugLZQ