PL/SQL converts rows and columns

This blog post describes the simple conversion of rows and columns, which is provided in 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.

Part1: column-to-row. Requirements:

The following example shows a table:

The table is defined as follows:

create table TEST_TB_GRADE (   ID        NUMBER(10) not null,   USER_NAME VARCHAR2(20 CHAR),   COURSE    VARCHAR2(20 CHAR),   SCORE     FLOAT ) 

Required to be converted:

A feasible PL/SQL statement is provided below:

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. user_name

 

The implementation result is shown in the requirement.

Part2: Convert rows to columns. Requirements:

The following example shows a table.

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 ) 

It must be converted to the following format:

An executable PL/SQL statement is provided below:

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, Course

 

The implementation result is shown in the requirement.

 

The purpose of this article is to give a specific example of a column-and-column conversion solution in Oracle. There is nothing to be circled. You are welcome to provide other implementations, and you are also welcome to criticize and correct them!

[Click "Green Channel" ----- "follow debuglzq" below to share progress ~]