Problems about vertical table
Author: dinya
Summary: In the development process, some demand for conversion of the display mode is often encountered, and our habitual calls are transitions to the horizontal table, and this article briefly explains the common vertical surface turf. Table problem.
This article is suitable for readers: Oracle primary, intermediate
System environment:
OS: Windows 2000 Professional (English)
Oracle: 8.1.7.1.0
text:
In actual applications, we often encounter the way to convert data display, such as turning the horizontal table to a vertical table, or converting a vertical table to a horizontal table, such as: the display method of the curriculum, the average salary of the department, etc. Happening. The following will be described below to give two common vertical surface turntable tables (the data in this example is: 1, two, third grade subjects).
Table Structure:
Create Table Test_Table (grade_id number (8), - grade: 1, first grade, 2, second grade, 3, third grade subject_name varcha2 (30), - Subject: including language, mathematics, foreign language, political and other subjects Max_score Number (8) - the highest points)
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:
Demand Description: View the account information existing in the system, and the account information of each grade is displayed in the format below:
Grade_id subject_name
1 language mathematics
2 Chinese mathematical politics
3 Chinese mathematics English politics
Analysis: In the result requested, the subject of each grade will become a record, and the subjects per grade are not fixed. Therefore, consider writing a function to resolve, enter the grade information, use the cursor to get all the account information of this 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 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 returnv_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 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; - System automatically shuts down 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 get the result:
SQL> SELECT DISTINCT A.GRADE_ID, TEST_FUN (A.GRADE_ID) Subject from test_table a;
Grade_id Subject
1 language mathematics
2 Chinese mathematical politics
3 Chinese mathematics English politics
Second conversion method:
Demand Description: Requires the grades, subjects, and highest information in the table, as shown in the format of the table below, if the grade is not open, its highest division 0 means:
Grade Chinese Mathematics English Politics
First grade 95 98 0 0
Second grade 86 90 0 87
Third grade 93 88 88 97
Analysis: This demand will turn the grade score and the account information from the longitudinal direction to a horizontal direction, so that it is necessary to judge its subjects, there is a subject, and there is no display 0. At this time, take into account the use of Decode functions. 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, '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 , 'Political', t.max_score, 0)) Politics from test_table TGROUP BY T.GRADE_ID The need to explain that two functions are written in the first conversion mode, and the two functions implement the same needs, the different needs. What is used in the two functions in the two functions, and the cursor is manually opened in a function. After the cycle is completed, it requires manual shutdown. The latter function uses for loops, and the system automatically turns off the cursor after the cycle ends. In the second conversion method, the DECODE function is used. For detailed usage of DECODE, please refer to the Oracle function related documentation.
to sum up:
The above two conversion methods are often encountered in the development, and other similar conversions in the development can refer to the above conversion mode, and some special processing can be made using functions such as decode, nVL. the way.
This article you can be in the author
Blog
Find, please visit the author
Blog
. Author Blog: http://blog.9cbs.net/dinya2003/ If reproduced, please keep the author blog information.
