Questions about converting a vertical table to a horizontal table

Author: dinya
Abstract: During the development process, we often encounter the need to convert the display mode of tables. We often call it vertical table to horizontal table conversion, this article uses an example to briefly illustrate the common problems of converting two vertical tables to a horizontal table.

This article is intended for: oracle elementary and intermediate

System Environment:

OS: windows 2000 professional (English version)

Oracle: 8.1.7.1.0

Body:

In practical applications, we often encounter the need to convert the data display mode, such as converting a horizontal table to a vertical table or converting a vertical table to a horizontal table, such: the display method of the curriculum and the ranking of the average salary of the Department. The following describes how to convert two common vertical tables to horizontal tables based on the requirements of the two real examples (the data in this example is: the highest score of each subject in the first, second, and third grades ).

Table Structure:

Create table test_table
(
Grade_id number (8), -- grade: 1, first grade, 2, second grade, 3, third grade
Subject_name varchar2 (30), -- Subject: contains Chinese, mathematics, foreign languages, politics, and other subjects
Max_score number (8) -- highest score
)

Table data:

SQL> select * from test_table;

Grade_id subject_name max_score

1 Language 95

1 mathematics 98

2. Language 86

2 mathematics 90

2 politics 87

3. Language 93

3 mathematics 88

3 English 88

3 politics 97

9 rows selected.

 

The first conversion method:

Requirement: view the information about the subjects in each grade in the system, and the information about the subjects in each grade is displayed in the following format:

Grade_id subject_name

1. Chinese Mathematics

2. Chinese Mathematics and Politics

3. Chinese mathematics and English politics

Analysis: In the expected results, the subjects of each grade are changed to a record, and the subjects of each grade are not fixed. Therefore, you need to write a function to solve this problem. input the grade information and use the cursor to obtain information about all the subjects of the grade and return the value.

1. Create a function:

SQL> create or replace function test_fun (p_grade number) return varchar2

2 v_temp varchar2 (100): =;

3 v_out varchar2 (500): =;

4 cursor c is select a. subject_name from test_table a where a. grade_id = p_grade;

5 begin

6 open c; -- open the cursor

7 loop

8 fetch c into v_temp;

9 exit when c % notfound;

10 v_out: = v_out | v_temp;

11 end loop;

12 close c; -- close the cursor

13 return v_out;

14 exception

15 when others then

16 return an error occured;

17 end;

18/

Function created.

 

SQL> create or replace function test_fun (p_grade number) return varchar2

2 v_out varchar2 (500): =;

3 cursor c is select a. subject_name from test_table a where a. grade_id = p_grade;

4 begin

5 for v_temp in c loop

6 v_out: = v_out | v_temp.subject_name;

7 end loop; -- the system automatically closes the cursor.

8 return v_out;

9 exception

10 when others then

11 return an error occured;

12 end;

13/

Function created.

 

2. Call the function to obtain the input result:

SQL> select distinct a. grade_id, test_fun (a. grade_id) subject from test_table;

Grade_id subject

1. Chinese Mathematics

2. Chinese Mathematics and Politics

3. Chinese mathematics and English politics

 

Second Conversion Method:

Requirement Description: The grades, subjects, and the highest information in the table are displayed in the following table. If a course is not offered for this grade, the highest score is 0:

Grade Chinese Mathematics English politics

Grade 95 98 0 0

Grade 86 90 0 87

Grade 3 93 88 88 97

 

Analysis: This requirement converts the score and subject information of a grade from vertical to horizontal. In this way, the score and subject information of each grade are determined. If a subject exists, the highest score of the subject is displayed, if no value exists, 0 is displayed. In this case, the decode function is used to solve the problem. The implementation is as follows:

Select
Decode (t. grade_id, 1, first grade, 2, second grade, 3, third grade) grade,
Sum (decode (t. subject_name, 'China', t. max_score, 0) language,
Sum (decode (t. subject_name, mathematics, t. max_score, 0) mathematics,
Sum (decode (t. subject_name, English, t. max_score, 0) English,
Sum (decode (t. subject_name, politics, t. max_score, 0) Politics
From
Test_table t
Group
T. grade_id

 

It should be noted that two functions are written in the first conversion method. The two functions implement the same requirement. The difference is that the two functions use different cursors, manually open the cursor in a function. You must manually close the cursor after the cycle ends. The next function uses the for loop. After the loop ends, the system automatically closes the cursor. The decode function is used in the second conversion method. For detailed usage of decode, see oracle function documentation.

Summary:

The preceding two conversion methods are often encountered during development. For other similar conversions in development, refer to the preceding conversion method and use decode, nvl and other functions perform some special processing to obtain the desired display method.
You can find this article on the author's blog. For more information, log on to the author's blog.

Author blog: http://blog.csdn.net/dinya2003/
If reprinted, keep the author's blog information.
Article: webmaster Sky web site: http://www.z6688.com/
The above information is an integral part of the text. If you want to reprint this article, please keep the above information. Thank you!