On the problem of turning horizontal table of vertical table

Problem
On the problem of turning horizontal table of vertical table
The author of this article: Dinya
Content Abstract: In the development process, often encountered a number of tables to display the need for conversion, we are accustomed to call the vertical table to the transformation of the table, this article through an example to briefly illustrate the common two vertical table to cross table problem.

This article is suitable for reader scope: Oracle Primary, Intermediate

System environment:

Os:windows Professional (English edition)

oracle:8.1.7.1.0

Body:

In practical applications, we often encounter the need to transform the data display, such as the conversion of the horizontal table to the vertical table, or the vertical table into the horizontal table, such as: the presentation of the timetable, the department's average wage ranking and so on. The following is the solution to two common vertical table cross tables based on the requirements of two instances (the data in this example means: The highest score of the subjects in the first and two or three 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),--subjects: including Chinese, maths, foreign languages, politics and other subjects
Max_score Number (8)--Highest score
)

Table of data:

Sql> select * from test_table;

grade_id Subject_name Max_score

1 Languages 95

1 Mathematics 98

2 Languages 86

2 Mathematics 90

2 Politics 87

3 Languages 93

3 Mathematics 88

3 English 88

3 Politics 97

9 rows selected.



The first mode of conversion:

Requirement Description: View the account information that exists in the system for each grade, and the subject information in each grade is shown in the following format:

grade_id Subject_name

1 Chinese mathematics

2 Chinese mathematical politics

3 Chinese mathematical English politics

Analysis: In the required results, the subjects for each grade will become a record, and the subjects for each grade are not fixed. So consider writing a function to solve, enter the grade information, use the cursor to get all the account information of the grade and return the value.

1, Build function:

sql> Create or Replace function Test_fun (p_grade number) return VARCHAR2 as

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 cursor

7 loop

8 fetch C into v_temp;

9 Exit when C%notfound;

Ten v_out:=v_out| | ' '|| V_temp;

one end loop;

Close C; --Close cursor

return v_out;

exception

When others then

' An error occured ';

The end;

18/

Function created.



sql> Create or Replace function Test_fun (p_grade number) return VARCHAR2 as

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 closes the cursor automatically

8 return v_out;

9 exception

Ten when others then

One return to ' an error occured ';

End;

13/

Function created.



2. Call function to get input result:

Sql> SELECT DISTINCT A.grade_id,test_fun (a.grade_id) subject from test_table A;

grade_id SUBJECT

1 Chinese mathematics

2 Chinese mathematical politics

3 Chinese mathematical English politics



The second mode of conversion:

Requirements Description: Requires that the grade, subject and the highest information in the table according to the format shown in the following table, if the grade is not open courses, the highest score of 0 means:

English politics of Grade Chinese mathematics

First Grade 95 98 0 0

Second Grade 86 90 0 87

Third Grade 93 88 88 97



Analysis: This demand will grade grades and subject information from vertical to horizontal, so that each grade should be judged on its subjects, the existence of subjects show the highest score of the subject, if there is no display 0. This is the time to consider using the Decode function to solve. Implemented as follows:

Select
Decode (t.grade_id,1, ' first Grade ', 2, ' second year ', 3, ' third grade ') grade,
SUM (Decode (t.subject_name, ' language ', 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 BY
t.grade_id



What needs to be explained is that, in the first conversion mode, two functions are written, two functions to achieve the same requirements, the difference is, two function of the middle of the use of different methods, the ground in a function to open the cursor manually, after the end of the loop requires manual shutdown. The next function uses a for loop, and the system automatically closes the cursor after the loop ends. In the second conversion, the Decode function is used, and for detailed usage of decode, refer to the Oracle function documentation.

Summary

The above two conversion methods are often encountered in the development of the situation, in the development of other similar transformations can refer to the above conversion mode, using DECODE,NVL and other functions to do some special processing to get the desired display mode.
This article you can find in the author's blog, more content please login to the author's blog.

Author blog:http://blog.csdn.net/dinya2003/
If reproduced, please keep the author blog information.